UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> How To Display Data From A Second Table In The Report Footer, Access 2016    
 
   
InfoHound
post Feb 6 2018, 03:50 PM
Post#1



Posts: 1,089
Joined: 1-December 12



I have a form that is based on a table. The main field in this table was derived from information from many other fields from different tables. I've created a separate table to total different things about the contents of the field on this table. Is there anyway to use one table to supply the report with the detail section and another table for the form footer section?

There is no relationship between the two tables so I'm at a loss on how to make a query that could handle the data.

Thanks

I got it to work by placing both tables in the same query without using a join. I just added the fields from the second table to the grid and it works perfect.
This post has been edited by InfoHound: Feb 6 2018, 04:20 PM
Go to the top of the page
 
Doug Steele
post Feb 6 2018, 04:58 PM
Post#2


UtterAccess VIP
Posts: 21,920
Joined: 8-January 07
From: St. Catharines, ON (Canada)


Glad you got it working, but having two tables in a query without a join may not be the best approach.

When you've got a query without a join, it's called a Cartesian Product. What happens under the covers is that each row in table 1 gets matched with each row in table 2, so that if you've got 10 rows in table 1 and 5 rows in table 2, you end up with 50 rows in the resulting recordset. That can lead to unexpected results.
Go to the top of the page
 
InfoHound
post Feb 6 2018, 06:23 PM
Post#3



Posts: 1,089
Joined: 1-December 12



You are 100% right Doug! It worked fine until I added a fifth field then it started showing four records for each one of the fields in the first table.

Any ideas or just re-think my whole approach?

Thanks
Go to the top of the page
 
projecttoday
post Feb 7 2018, 06:49 AM
Post#4


UtterAccess VIP
Posts: 10,069
Joined: 10-February 04
From: South Charleston, WV


What's wrong with a subform?
Go to the top of the page
 
InfoHound
post Feb 7 2018, 11:00 AM
Post#5



Posts: 1,089
Joined: 1-December 12



Because I'm making a report there is a look I'm going for that a subform won't satisfy.

Here's what I did.(comments of any kind are welcome)

I created a table that holds only one record with all the totals and another table that holds all the records so that a history of events can be maintained.
I delete the record in the table that I use for the report then I add the new record. Because this table only holds one record the product of the Cartesian does not produce multiple lines for the same record. I does what I want, but, I'm still not sure if that is what real programmers do?
Go to the top of the page
 
projecttoday
post Feb 7 2018, 11:06 AM
Post#6


UtterAccess VIP
Posts: 10,069
Joined: 10-February 04
From: South Charleston, WV


In post #1 you say you're making a form. So which is it?

Yes, programmers sometimes use temporary tables for reporting.
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    25th September 2018 - 09:48 PM