Union Query or not! 3

[diggerbob]

I am a beginner to intermediate access user. I have recently gotten a new job and my first assignment is to change the company's records from Excel to Access. I have done most all of the work and things have gone good. I have come across a problem. My supervisor has asked for a report and I can't get it to work.
Our main product purchased is coal and it comes from 3 suppliers. I have tables, forms, queries, and reports already made that shows daily deliveries of all the suppliers. The one thing that has not been tracked is what my supervisor orders at the beginning of the month. He orders one total amount through a broker (i.e. 60,000 tons). What he wants to see at any given time during the month is the total amount ordered (60,000 tons), each individual suppier's "to date" total coal hauled in and the balance of what needs delivered for the remainder of the month and he only needs to see summary figures(no details). The report should show the following:

Total Coal Ordered
minus
Total Coal Delivered (showing each supplier's up to date total individually)
=
Balance left to deliver

I've tried a join query and a union query but the problem is that the table that I made for "Coal Ordered" is only one date(the first of the month) and my "Supplier" table has every day of the month in it. I would appreciate some help in this and if someone needs more info, please let me know.

 

[PHV]

Have you tried to play with the Month and Year functions ?
Or with Format(anydate, "yyyymm") ?

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[formerTexan]

Diggerbob,

This is about where many people became acquanted with normalization and table design issues. If tables aren't set up correctly, data retrieval will become difficult at best.

Without knowing more about how your tables are constructed, its difficult to give a specific answer, so I will throw out some questions and very general suggestions.

Why does your Supplier table have every day of the month in it? Hopefully it is because you are getting deliveries every day.

Why does the Orders table have only one date. Presumably all orders are placed on the first of the month (though that strikes me as unlikely - weekends, holidays, etc.).

What you should have is an Orders table with a Purchase Order Number (PO) or something similiar. Date fields may be included for the date the order was placed and perhaps the tentative delivery date (if more than one date, then a seperate table should be set up). Other pertinent information or links to pertinent tables should also be included.

Then, since deliveries for a particular order seem to be ariving in several parts you should have a delivery table. This must include the PO so that you have some way of relating the delivery to the Order. Also a date, amount, supplier link, etc.

Queries and reports should then become less complicated.

Come back with your table structure and I or someone else will make specific suggestions.

Cheers,
Bill

 

[diggerbob]

Bill,
The orders table has only one date. I have chosen the first day of the month for consistency. The coal is ordered in the prior month at any given time for the following month. The supplier(delivery)table has only the individual dates that coal is delivered, which is 6 days a week from 3 different suppliers. I need to do a running sum also so that I can use a date range form so that my manager can look at this summarized at any given time during the month. If I have time over the weekend, I will send some structure. How is the best way to post this structure?
Thanks,
Bob

 

[jksmi]

Hi diggerBob,

you can add a DSum expression to your query, i.e. in Design View type in a blank field

NET_TRANS: nz(DSum("TRANS_AMOUNT","TRANSACTIONS","[TRANS_DATE] <= Format(#" & [TRANS_DATE] & "#,'mm/dd/yyyy')"),0)

where TRANS_AMOUNT is the column you wish to running sum, TRANSACTIONS is your table and TRANS_DATE is your date column name...

HTH, Jamie
FAQ219-2884

 

[willir]

Bob

Do you have a raw material table, or consumables or inventory or commodities where you track the purchase of this type of commodity? (Other commodities could be gas) I am not sure of the nature of your business so it is a little awkward to provide specifics.

You then need a table track purchases, and the supplier table. (Depending on your needs, purchase and delivery could be tracked on the same or different tables)

Most designs would include the purchasing or invoice table and the supplier table, but unless you are a manufacturer (raw material -> finished product), it would be easy to forget about the raw materials.

This would make the query fairly easy...
For this commodity, find all purchase for this month and group by supplier.

You want structure... here are some sample posts...

http://www.tek-tips.com/viewthread.cfm?SQID=628486&SPID=700&page=1

http://www.tek-tips.com/viewthread.cfm?SQID=844594&SPID=705&newpid=705&page=1

BTW, there is a forum on design...

http://www.tek-tips.com/threadminder.cfm?spid=700

Richard