Union Query?If Statement?Running Total?

[Elvis72]

Its been a few years since I have really gotten my hands deep into creating a new database. So, I'm at a cross roads as to the best way to accomplish my goal.

What I need is something that takes this:

Invoice Number Client Number Working Office Budget Amount Amount Total Amount
1234 13 13 79.54 13.45 64.15
1234 14 13 153.21 13.45 143.97
1234 15 13 532.01 13.45 345.43

And makes this:

Invoice Number Client Number Working Office Budget Amount Amount Total Amount
1234 13 13 765.32 40.35 553.45
1235 14 14 235.32 23.21 85.46
1235 13 13 13254.02 23.24 12354.34

All of this information is in one query that does a currency conversion, but it all comes from one table.

Any questions please ask!~

Thanks so much!~

 

[dhookom]

Am I missing something? What type of conversion are you performing? How does 79.54 become 765.32?

Duane MS Access MVP
Now help me support United Cerebral Palsy

 

[Elvis72]

The conversion is currency...if the math is inaccurate its because you are not seeing the entire invoice.

The conversion is irrelavent...I just want to merge all the multiple records into one based on the invoice number and the working office number, with calculated totals for the dollar amount fields.

 

[PHV]

Why not use an aggregate query ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Elvis72]

From my experience if you are looking to group multiple records together and get a total for that group while seeing all the rest of the records in a table or query an append will only give you the total for the column as a whole with no grouping possible.

If not please point me in the right direction!~

 

[lespaul]

First off, your post makes no sense. Usually, the result are somewhat related to the sample data.

You want to change the invoice number from the original to the result set? Your client numbers change, the working office changes, if you want to change your budget amount from 79.54 to 765.32, how did you do that? I think checking your sample and expected results might be in order so that maybe we get a better idea of what you need.

Now you are asking about appending?

Here's some information from another thread where I explain aggregates and grouping that perhaps will help:
[tt]
An aggregate function performs based on all the other fields in your select.

If you have a table like:

Salesman SaleDate Amount
JR 1/1/2006 $500.00
EM 1/1/2006 $250.00
JR 1/1/2006 $100.00
EM 1/2/2006 $101.00
JR 1/2/2006 $75.00
JR 1/2/2006 $100.00

and you want to sum the sales for the month for EACH salesman:

SELECT Salesman, SUM(Amount) FROM TableName GROUP BY Salesman

the results would be:

JR $775.00
EM $351.00

If you want to know how much per day:

SELECT SalesDate, SUM(Amount) FROM TableNmae GROUP BY SalesDate

the results would be:

1/1/2006 850.00
1/2/2006 276.00

If you want by Salesman, by Date:

SELECT Salesman, SalesDate, SUM(Amount) FROM tableName GROUP BY Salesman, SalesDate ORDER BY Salesman, SalesDate

EM 1/1/2006 250.00
EM 1/2/2006 101.00
JR 1/1/2006 600.00
JR 1/2/2006 175.00

So, yes, by adding additional fields to your select, you will have different SUMs.

Can you see what the "GROUP BY" is doing now? It's GROUPING together like information and performing the aggregate function in the SELECT on that GROUP. [/tt]




Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com

 

[Elvis72]

Leslie -

Sorry for the confusion, I should have explained myself better in the above example.

Yes that is the desired outcome I am looking for but I have never used an aggregate query before that was my question.

Thank you for your help!~