Report based on unrelated tables

[DebbieV]

Hi All,

Is it possible to create a report based on info that is in tables that cannot be related? I want to create a report that compares the amt of items reported delivered to the amt of items shipped. I have created forms for this info to be entered in, so the info is in the tables but I cannot relate these two tables. I tried using an auto number and making that the foreign key in the related table.
This won't work because the person entering the data would need to know what the auto number in the primary table. I also tried using a license number, but a licensee could have several deliveries in a year and would not filter the particular month but list all deliveries for that license number, I was also filtering by month.
So is anyone has any ideas on how to make this happen, please share.

DebbieV

 

[Pat Hartman]

There must be some identifying data in the tables. How would you decide which row in one table should be compared to which row in the other table?

 

[DebbieV]

I have two tables one Deliveries the other Shipments; the fields in the Deliveries table are DelivID, Supplier Name, Gallons Recd, & CalendarMonth. In the Shipments table the fields are; ShipmentID, WholesalerName, GallonsShipped, SCalendarMonth, and DelivID. This DelivID foreign key is not good because the data entry person would have to go back to the tables to find the matching primary key. I tried adding the license number of the supplier and making it a foreign key in theShipments table and just linking those two fields in the query, but then I noticed it showed all the records for a particular licensee. In the query I had a criteria for the calendar month, but by linking the license numbers it would list all the months and the filered one. Maybe I could go back and make the license number the primary key and allow duplicates.
Well let me know if you have any good ideas on this.

DebbieV

 

[Pat Hartman]

The logical thing to use to match these two tables is a product ID but neither table seems to contain anything like that. You have something coming in and something going out. But what?

 

[DebbieV]

we are comparing gallons of alcohol, suppliers reported shipped to wholesalers verus gallons wholesalers reported received from suppliers. to verify if there is a difference in the reported amts.

DebbieV

 

[Pat Hartman]

So since you never deal with any other product, just use a totals query to sum the quantity for a time period.

query1:
Select CalendarMonth, Sum([Gallons Recd]) As SumGalRecd
From Deliveries
Group By CalendarMonth;

query2:
Select SCalendarMonth, Sum([GallonsShipped]) As SumGalShip
From Shipments
Group By SCalendarMonth;

query3:
Select query1.CalendarMonth, query1.SumGalRecd, query2.SumGalShip
From query1 Inner Join query2 On query1.CalendarMonth = query2.SCalendarMonth
Order by query1.CalendarMonth;

query3 can be used as the recordsource for a form or report. If you only want to see a single month, you can add a parameter to limit the rows selected.

BTW, have you given any thought to what will happen when next year comes around and you have more than a single year's worth of data in the table?

 

[DebbieV]

Hi Pat,

Hey that works great! Thank you very much. I really like Access and am always willing to learn how to make it perform. Any suggestions on classes to take to help me become more knowledgeable? I am just getting into taking programming C++ here at a MD college.
As far as your question about this database storing records for the following year. In my past experiences the databases I have created continue to accept data for each year and we just use the filter. Depending on the need sometime only fiscal year info is needed, so I copy the datatbase for the next year and then delete last year info.
So in this instance I would just enter the month and year for the info I am looking for.

Thanks
DebbieV