sql query help needed

  • Thread starter Thread starter Jim Chapman
  • Start date Start date
J

Jim Chapman

Hi,

I'm somewhat new to sql and need help with the following.

Need to return one line that combines tax from the following data

InvoiceNumber Item Description Total
1003 Pencil A Pencil 1.00
1003 Tax Pencil .08

would like to have it return the following
item price tax total
Pencil 1.00 .08 1.08

The Item will alway be in the tax description

thanks
jim
 
Dear Jim:

Are there always 2 lines for each InvoiceNumber, with one Tax and one
not? Or could there be a Pencil, a Pen, and then Tax?

Does the Tax row Description always match an Item? How would the
invoice look if there were a Pencil, a Pen, and Tax? And how would
the desired output look for this?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
About 95% of the items will have a proceeding tax line but there are
situations that will not have a following tax line.
Does the Tax row Description always match an Item? How would the
invoice look if there were a Pencil, a Pen, and Tax? And how would
the desired output look for this?
Click to expand...

Yes, the Tax lines Description will always match the Item it is
representing.


InvoiceNumber Item Description Total
1003 Pencil A Pencil 1.00
1003 Tax Pencil .08
1003 Pen A Pen 2.00
1003 Tax Pen .16
1003 Stamp Postage Stamp .33 <- no tax line after
this one.


Output for the stamp would be
Stamp .33 .00 .33
 
Dear Jim:

OK, not what I was thinking at all. Thanks for clarifying.

I’m now thinking of a self-join of the table to itself, with the
non-Tax item on the left side of the JOIN. Something like this:

SELECT T1.Total AS ItemTotal, Nz(T2.Total, 0) AS TaxTotal,
T1.Total + Nz(T2.Total, 0) AS GrandTotal
FROM YourTable T1
LEFT JOIN YourTable T2
ON T2.InvoiceNumber = T1.InvoiceNumber
AND T2.Description = T1.Item
WHERE T1.Item <> “Tax” AND T2.Item = “Tax”
ORDER BY InvoiceNumber, Item

You must change "YourTable" above to the actual name of your table.
The WHERE clause is just for insurance. You might try it without to
see if it makes any difference, and if it is a good difference that it
makes.

Please let me know how this comes out.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Back
Top