Query total into report (1 Viewer)

[mounty76]

Hello!

I have several queries that pick out several results from a table then total the invoices together and on the query it says the total at the bottom.

How can I put ONLY the total from the query into a report.

It must be simple but I'm completely stuck on it!!

Thanks in advance

 

[plog]

Most likely you would recalculate that total with a DSUM:

DSUM function - Microsoft Support

Syntax: DSUM(database, field, criteria)

support.microsoft.com

Set the control source of the input to:

=DSum("[FieldName]", "QueryName")

 

[mounty76]

Amazing, thank you!!! If only I'd known a few hours ago!! hahahaha, thanks again

 

[plog]

I'm with you and welcome to coding. You spend hours killing yourself to find out the answer contains just 20 characters.

 

[Galaxiom]

I'm with you and welcome to coding. You spend hours killing yourself to find out the answer contains just 20 characters.

Problem is so often having to find out what doesn't work first.

I worked in a place once where another non-computer-literate employee claimed I wasn't very good at my job because it usually took me ages to just make the one small change that was needed to fix problem.

 

[mounty76]

Hi Gents....another one for you....again the last 4 hours and I'm no closer!!! Haha

In my table I have:

CODE Invoice Cash Creditcard

1.02 XXX $1000 $50 $150
1.02 XXX $500 $1000 $100
1.03 XXX $70 $500 $150
1.03 XXX $500 $1000 $100
2.01 XXX $1000 $50 $150
2.01 XXX $500 $10000 $100


Is there a way I can get the results to look like this:

1.02 XXX Total $2800
1.03 XXX Total $2320
2.01 XXX Total $11800

Hope that makes sense, trying to add all payments made for each record then group the records together by the Code and total it all up.....my brain is fried!!!


Thanks again

 

[plog]

SELECT CODE, SUM(Invoice + Cash + Creditcard) AS Total
FROM YourTableNameHere
GROUP BY CODE
ORDER BY CODE

 

[mounty76]

Thanks for this, it groups together all the Codes and shows a total column next to them but there is no data in the total column

 

[plog]

You probably have NULL values in some of the fields that comprise Total. Fix it with NZ:

SELECT CODE, SUM(NZ(Invoice,0) + NZ(Cash,0) + NZ(Creditcard,0)) AS Total
FROM YourTableNameHere
GROUP BY CODE
ORDER BY CODE

 

[mounty76]

It now returns a 0 (zero) in each total ?

 

[plog]

Play with it, verify it, tweak it. Focus on 1 set of records (e.g. code =1.02 XXX), filter the query I gave you to just that CODE, then build another query to see the individual records that should be going into the totals query.

Then when all that fails, upload a sample database.

 

[mounty76]

Hi,

Tried everything and still got no where, db attached. It is setup so that when you complete a filtered search it then pastes the search results in to the TempTable folder.

The Query I'm struggling with is the PL Query, basically need it to group each code together and total all the invoices,cash,etc. for each group

Sounds simple but I'm really stuck on this!

 

[plog]

Total: Sum(Nz(Invoice,0)+Nz(Cash,0)+Nz(Soldo,0)=Nz(Centtrip,0))

You missed the shift key on the last plus symbol

 

[plog]

Looking at it, you have bigger problems.

1. Why do you have a table called TempTable? Tables should exist or not exist, not live in a state of impermanence.

2. TempTable has an ID field, but its Short Text and all records are Null. That defeats the purpose. You should have an autonumber primary key for the ID.

3. You need to put your amounts in a new table. Since you need to sum so many fields (3 was the limit, but 4 is too many), you need a new table that has just 1 amount field and allows you to assign a type. It would look like this:

TempTableFunds

ttf_ID, autonumber primary key
ID_TempTable, number, foreign key back to TempTable's primary key
ttf_Type, short text, this will hold what is now the name of those 4 fields (Invoice, Cash, Soldo, Centrip
ttf_Amount, number, this will hold the amount


Then when you need to sum you don't have a complex SUM formula with a bunch of NZ calls, you simply do this:

SELECT ID_TempTable, SUM(ttf_Amount) AS Total
FROM TempTableFunds
GROUP BY ID_TempTable

You really need to read up on normalization and not use temporary tables.

 

[mounty76]

Thank you very much for the info, make sense now looking back at it