Getting a summary from query into a table

[melanie]

Hi all

Hoping there is a simple answer to this question:

Here goes:

I have a table called "container details" pk is "idnumber" and another which is linked to it called "pallet details".

So one container to many pallets.

The reports I generate is a pack list which shows all the entries for the pallet details and then another one which comes from a query which summarizes the pallet details -

eg. the pallet details are entered as follows:

pallet 1 apples red 150 cartons
pallet 2 apples red 150 cartons
pallet 3 apples green 150 cartons
pallet 4 apples red 150 cartons
pallet 5 apples green 150 cartons


the second report - the pack list summary report will show:

IDnumber so and so =
apples red 450 cartons
apples green 300 cartons

here comes the tricky bit:

I need to generate invoices on these summarised amount and the invoice price changes daily, also there are five different types of invoice to be generated with each container.

I have fiddled around with the append query, but don't seem to be having much luck with it - it keeps duplicating the records.

I have now made a table called "invoice details" which has as its primary keys:

idnumber
product
variety
size

but now the append query won't run due to key violations and I'm not sure if its the right way to go.

In the end I need the pack list summary from the query in order so that I can insert my invoice values against them.

Your help gratefully wanted.

Melanie

 

[Rich]

You only need to store the details of the invoice and the price applicable at the time of say despatch, the rest can be calculated via the query you already have, just base the Invoice on the query

 

[RichMorrison]

Melanie,

I don't have an example for this type of thing, but you will probably find one in the standard "Northwind" example MDB that comes with Access.

HTH,
RichM

 

[melanie]

invoices

Rich ....and .... Rich 2

thank you for your quick response -

firstly I've scoured the northwind sample before, but I don't think it applies in this case.

The situation is such that the invoices need to be generated after the pack lists becuase I need to work off the query in order to get to the summary -

the apples will be invoiced a different price to the oranges, for example. Not only that - there are different sizes of each and they are different prices. Each invoice (there are five) has different prices. Advance payment prices, insurance invoice, fob invoice, proforma fob (in some, not all instances) clearance invoices. Some of them are in different currencies, depending on where the product goes and where its from.

Are you suggesting that all of these invoices prices go into one table, each with its own currency column?

The invoices need to reflect the summary of the pack list.

Any other thoughts.

Melanie

 

[melanie]

Another way to do it Maybe

Hi guys...

been thinking....(I seem pretty doff, but occassionally flashes occur....)

is it possible to put some vb behind a command button on the main pack list form which will run the query which summarizes all the details on the pack list form and which will then append only those records matching the idnumber which is open on the form to the invoices table so that I can immediately go to the invoices form and the summary details will be showing so that I can enter the invoice amounts against the product being shipped.

I would need it to append the id number, then the productid, varietyid, sizeid and the number of cartons.

The product being shipped is on consignment which is why I dont have a table for the cost of the product.

Awaiting your response with clenched teeth, white knuckles, wide eyed and staring.

Another sleepless night ahead......

Thanks

 

[melanie]

Many invoices - the wood for the trees

Hi there again.

Thank you for the advice about the query

I've managed to create the append query (it didn't work before due to key restrictions) which appends to an "invoice details" table which is then linked to the invoices table.
I then based the invoices form on a query which joins the invoice details table and the invoices table, setting the join to the second option in the query relationships window. (Think that made the difference in the end because before that the form was coming up blank or with the appended records, but then I couldn't enter invoice values.)

I've created the five invoice tables separately, however, due to the currency issues, but maybe if I could set the currency via a currency combo list to the invoice amounts for the particular shipment on the form then I could use the five invoice amounts in one invoice table. Your advice needed.


One thing is worrying me though and that is ...if there
are ammendments to the shipping documents, how do I change just the edited items and do that automatically so that the invoices details table is updated without user intervention. Do I use vba on maybe the "change even" of the pack list form? Do I do an update query or delete query and then run the append query to fill in the missing records??

Thanks again

Melanie