Re: Query
- From: "Ron2005" <Ron.Nemec@xxxxxxxxxxx>
- Date: 30 Dec 2005 08:52:43 -0800
OK..... If we do this a few more times maybe we will understand each
other and I will understand what you are looking to produce.
Let me rephrase what I think you want. You want to produce a report by
product that shows the sum of all invoice amounts for a selected month
and the sum of all Credit amounts for that selected month and the net
of the two amounts.
Lets take the following example with the assumption that these are all
in the same month.
Invoice 1 Product A
Invoice 2 Product A
Invoice 2 Product B
Invoice 3 Product C
Credit 1 Product A
Credit 2 Product B
Credit 3 Product X
because of that last credit I believe you will need to create a table
to house the intermediate results
Report Table
Product # Invoice Amt Credit Amt Net Amt (This is
optional but clearer)
1) Query to empty table
2) Sum query for Invoices and append for all products (selected by
month)
Append the Product and the Invoice Amount
And the Net amount should also be loaded with the Invoice
Amount
3) You need to create a sum Query for the Credits by product - selected
by month
4) Create an append query for the table that links the table with the
step 3 query but that has a condition that the Table product id is
null. (Link criteria says All itmes in Credit Query and those from
table that match BUT there is an additional criteria on the Table
product ID field that it is null) This will add the Product X to the
table
5) Update query with Credit Query matched to Sum Table by product ID
put the Credit amount into the Credit field (You may want to
multiply it by -1 to have it look like a credit.)
(Or use formula [tblCredit] - [qryCredit Amt])
update the Net amount with formula of [net amount] - [credit
Amount]
You now have a table with your report in it and can display/report it
with whatever other queries please you.
Ron
.
- Prev by Date: Re: Ascending Combobox pulling from table field
- Next by Date: Re: How can I Save Link/Path to Record After Browsing Folders?
- Previous by thread: Re: Query
- Next by thread: panel equivalent
- Index(es):
Relevant Pages
|
