Creat a Summary Report

[Karenclaire]

Hi there,

I have created a db in access to track our purchases (LPO) and deliveries against each LPO for that i have created 2 tables

1. PO
2. Deliveries

now i want to create a report which can sums up or summarize in a way that i can be able to see deliveries made against each LPO issued.

keep in mind that we issue bulk LPOs and deliveries can be made in parts against each LPO.

i have kept the PO number as common field in both the tables.



i hope i have defined my question properly

waiting for your helpful tips and suggestions

[rpeare]

Create a query based on your deliveries table.
Make it an aggregate query (look for the sigma button, looks like a capital E on your toolbar)
On the TOTALS line of your query you will want

GROUP BY for your PO number and ITEM lines you will want SUM on the number of the item delivered.

This should give you a summarized query of your deliveries.

Save this query with whatever name you want.

Create a new query based on your PO line items
Link those to your summarized query.

Now you can display the total number ordered vs the total delivered and the remainder to be delivered is a simple calculation of ordered vs shipped.

[Karenclaire]

Thanks for the reply,
but I am quite new to access to let me rephrase my question and i am attached some screen shots of my db to explain more effectively (hope so)





now i as you can notice that i have in total 4 tables where i am saving my data in (might be doing wrong too)

so to extract report from these 4 tables i am getting the headace i tried using your suggestion but didn't worked

can you suggest anything in these please

Create a query based on your deliveries table.
Make it an aggregate query (look for the sigma button, looks like a capital E on your toolbar)
On the TOTALS line of your query you will want

GROUP BY for your PO number and ITEM lines you will want SUM on the number of the item delivered.

This should give you a summarized query of your deliveries.

Save this query with whatever name you want.

Create a new query based on your PO line items
Link those to your summarized query.

Now you can display the total number ordered vs the total delivered and the remainder to be delivered is a simple calculation of ordered vs shipped.

[rpeare]

can't really tell anything just by looking at screenshots but my first post should do what you want. You are summing all the items delivered against a PO (I don't know what you're storing in your deliveries table but you should not need that at all, deliveries go against a PO from the look of it) then linking that back to the original line item to see how much you have left to deliver. As long as you have a way of linking your delivery detail to your po detail that's all you really need.