Problem with Report Calculations

[HMJ]

I am building reports from a 'logistical' database. Part of this database includes items (description and qty) that individuals are signed for. This can be up to 40 different descriptions per person. In another table is the current cost of these items.

What I have been trying to do is create a query where the items from table 1 are merged with the cost from table 2. The intent is to multiple the qty by the cost to get the total value. However, I get NO records out! I have tried every different type of linking between the tables I can think of.

How can I get this to work so I can generate a report showing what HMJ is signed for and the total cost of all these particular items?

Thanks.

 

[randysmid]

Hi,
It sounds like you need a report that will group by the individual. You can do this with "sorting and grouping", as found under the "View" menu.
Your query should just link the two tables together, and this becomes the recordsource of your report. So, if you were inside the query tool and ran it, you might get the following recordset:
HMJ 1234 (item #) 1.00 (price) 2 (qty)
HMJ 2345 1.50 1
HMJ 3456 1.00 6
RMS 1234 1.00 3
etc., etc.

Then, with sorting and grouping, you can then display information about the person in the group header, the detail records will show all of the items the person has checked out, and the group footer will add up the costs of these items (for HMJ, that should be $9.50, and the total quantity is 9). Is this what you are looking for?

Please reply to this post if you need help setting up the "sorting and grouping". HTH,
Randy Smith
California Teachers Association

 

[HMJ]

The problem is that I have three tables involved - one with the name, ssn, etc; one with the ssn (linked back to table 1, item descriptions and quantities; and the third table with the item descriptions and the cost each, which is not linked in any way to the other two.
I can query to merge the infor from tables 1 and 2 to get the name and items assigned (call it query 1+2) - no problem. It is merging the cost info from table 3 to this result (Query 1+2) in another query or a report to obtain the costs per item and total cost of all items that I can't get to work. In a query I get no result whatever. And I can't figure out how to make a report based on Query 1+2 and bring in the costing info from table 3 to make the math work.
Explain it better? Harry Jessen
HMJessen@Yahoo.com

 

[randysmid]

Hi,
If I understand you correctly, your third table with descriptions and cost does NOT have a unique key. Is this correct?
HTH,
Randy Smith
California Teachers Association

 

[HMJ]

Randy,
The only key in the third table is the Primary key assigned by Access. There was no way I could see to apply a relationship between this table and the other two.

Does this help, or cause a problem?

Thanks,

Harry Jessen
HMJessen@yahoo.com Harry Jessen
HMJessen@Yahoo.com

 

[randysmid]

Hi,
Since this key does not relate to any of the fields in the other tables, you can still make a match. Go to the query, and add the 3rd table. The table will now appear in the query window. Click on the itemDescription field in one table, and drag to the itemDescription field in the 3rd table, then release the mouse button. Right click on the line connecting these two tables together, and select Join Properties. Select the most appropriate join type. Obviously, someone could have deleted one of your itemDescription records, thereby leaving orphans.
HTH,
Randy Smith
California Teachers Association

 

[randysmid]

Harry,
You can also find out if you have any unmatched itemDescription records in either table. You can do this by creating a new query, and then using the "Find Unmatched Query Wizard". The first step will be select the table (e.g., tblNumber1) you want to see that will contain records that don't appear on another table (e.g., tblNumber2). Simply follow the wizard instructions. You may want to create a 2nd query like this, and flip the tables. For instance, select tblNumber2 to display and compare against tblNumber1.

Finally, you mentioned that one of the tables has the itemDescription, but not the unique key. You can write an update query to get that info into the table. You will need to first add the field to the table layout. HTH,
Randy Smith
California Teachers Association