Obtaining 3 separate totals in one query (1 Viewer)

eduardo · Jul 22, 2014

Hi there, I'm having a hard time figuring this out (I thought it was way simplier).

I have three tables A, B and C.

I want to build a query that displays three separate totals: Total of A, Total of B and Total of C.

When I try to do this, the query has a weird behaviour summing the totals of the totals of the totals, and I don't know why :banghead:

I'm attaching a really basic sample of what's happening, where totals SHOULD be $4, $8 and $16 instead of $16, $32 and $64.

Thanks!

MarkK · Jul 22, 2014

It doesn't make sense to me to have one query do summaries on three different tables. What actual data is in the tables?

eduardo · Jul 22, 2014

MarkK said:
It doesn't make sense to me to have one query do summaries on three different tables. What actual data is in the tables?

Well it does to me, I need a form to display the three independent totals.
The tables contains values of three different type of loans. Their grand totals are unrelated.

So, anyone thinks this can be made?

MarkK · Jul 22, 2014

All your loans should be in the same table. Add a field called "LoanType" that stores your A, B, or C designations, and then you can do things like . . .

Code:

SELECT 
   Sum(IIF(LoanType="A", LoanValue, 0)) As TypeATotal,
   Sum(IIF(LoanType="B", LoanValue, 0)) As TypeBTotal
FROM tblLoan

Having the same structured data in different tables is a design error.
Hope this helps,

eduardo · Jul 22, 2014

MarkK said:
All your loans should be in the same table. Add a field called "LoanType" that stores your A, B, or C designations, and then you can do things like . . .

Code:

SELECT Sum(IIF(LoanType="A", LoanValue, 0)) As TypeATotal, Sum(IIF(LoanType="B", LoanValue, 0)) As TypeBTotal FROM tblLoan

Having the same structured data in different tables is a design error.
Hope this helps,

I appreciate your suggestion regarding the design, but the each type of loan needs separate autonumbering, physically, we have A-B-C folders stored in different rooms. So we can have a A-8540 folder and a B-8540 folder. (unrelated)

That is the reason why I can't have them in the same table. Not saying my design is perfect, but I don't believe it's that bad.

Anyway thank you

Frothingslosh · Jul 22, 2014

Autonumber fields should never contain real-world data. They are there to allow for primary keys and table linking, not data storage. If you need your folders numbered sequentially, it's trivial to have a form do that as new entries are made.

The reason you're having so much trouble is that your tables are not normalized - you have virtually identical data in multiple tables and are trying to relate them. If you were to combine them the way MarkK suggested, you would find working with the entire database to be much easier.

If you leave things as-is, you're just going to run into thornier and thornier problems as your database develops.

jdraw · Jul 22, 2014

eduardo,

I agree with Mark. I believe you have a design issue. Further, I think you are misusing autonumbers. If you need a numbering scheme to match your room designations, then create fields for your use. Autonumbers are intended to be unique numbers only --NOT Incremental/sequential and not necessarily positive.
See this for more info on autonumbers.

Good luck with your project.

eduardo · Jul 22, 2014

Frothingslosh said:
Autonumber fields should never contain real-world data. They are there to allow for primary keys and table linking, not data storage. If you need your folders numbered sequentially, it's trivial to have a form do that as new entries are made.

The reason you're having so much trouble is that your tables are not normalized - you have virtually identical data in multiple tables and are trying to relate them. If you were to combine them the way MarkK suggested, you would find working with the entire database to be much easier.

If you leave things as-is, you're just going to run into thornier and thornier problems as your database develops.

Thanks for the tip, I wish I could redo the whole db as you say, but at a first glance it will be a massive task. I'll think about it. Thanks!

jdraw · Jul 22, 2014

They're always massive at first glance.
Do some research on Normalization. The sooner you do some tests with Normalized data, the sooner you'll see that task is not so large that it shouldn't be done. And you will learn a lot in the process.

vbaInet · Jul 22, 2014

eduardo said:
Thanks for the tip, I wish I could redo the whole db as you say, but at a first glance it will be a massive task. I'll think about it. Thanks!

Obviously I agree with everyone regarding the table structure but if you want to do what you're trying to do, is there any need of displaying all three in the same query if they're unrelated?

If all you want to do is display them side by side, create 3 separate totals queries, use the DLookup() function in 3 separate textboxes to get each of the values from the queries. Then put the textboxes side by side.

Otherwise, if you really need them side by side in a query then you can still create 3 totals queries, ensuring that they all return only 1 summed up value, then create another query and pull those three new queries into the new one.

eduardo · Jul 23, 2014

Ok I've been testing a new possible structure of the db based on your advice (one table for all types of loans) where autonumber is not related to real-world data.

So if I were to add a new loan, for instance, loan type "H", shall I add the new loan number manually?

In this attached picture, the last loan "type H" was the number 8003 (West Co.)
If any user of my database wants to add a new "H" loan, it has to be the 8004 and if anyone adds a loan type "V" it has to be 1273.

I cannot trust users this task because it could lead to confusions/mistakes. So can this be automated? As far as I know there can only be a signle autonumber field in access?

jdraw · Jul 23, 2014

See this example using DMax() +1
http://www.databasedev.co.uk/automatically_increment_value.html

Here's another example showing single user database and multiuser database.

http://rogersaccessblog.blogspot.ca/2010/12/domain-function-example-simulate.html

Good luck.

jdraw · Jul 23, 2014

Cross posted
http://www.utteraccess.com/forum/Automatically-Assign-Num-t2020100.html&gopid=2477347#entry2477347

MarkK · Jul 23, 2014

To get the Max + 1 for any LoanType you could write functions like . . .

Code:

Function GetNextLoanNumber(LoanType As String) As Long
[COLOR="Green"]'   Returns the next loan number for the given loan type[/COLOR]
    GetNextLoanNumber = GetMaxLoanNumber(LoanType) + 1
End Function

Function GetMaxLoanNumber(LoanType As String) as Long
[COLOR="Green"]'   Returns the max loan number for the given type, or zero[/COLOR]
    GetMaxLoanNumber = Nz(DMax("Number", "tblLoan", "LoanType = '" & LoanType & "'"), 0)
End Function

You can also add a unique index to the two fields LoanType and LoanNumber. Then the table will refuse any new records where the LoanType/LoanNumber combination being added already exists.

eduardo · Jul 23, 2014

The DMAX shown in the sample is useful but not in my case, it can't generate different sequences of numbers based on the "type" that I explained above.

In regard to the 'cross post' complaint, does it infringe the forum terms of use or something?
I don't see what's the point of posting a link to whatever I've might posted out there.

eduardo · Jul 23, 2014

MarkK said:
To get the Max + 1 for any LoanType you could write functions like . . .

Code:

Function GetNextLoanNumber(LoanType As String) As Long [COLOR=Green]' Returns the next loan number for the given loan type[/COLOR] GetNextLoanNumber = GetMaxLoanNumber(LoanType) + 1 End Function Function GetMaxLoanNumber(LoanType As String) as Long [COLOR=Green]' Returns the max loan number for the given type, or zero[/COLOR] GetMaxLoanNumber = Nz(DMax("Number", "tblLoan", "LoanType = '" & LoanType & "'"), 0) End Function

You can also add a unique index to the two fields LoanType and LoanNumber. Then the table will refuse any new records where the LoanType/LoanNumber combination being added already exists.

Ops, I didn't get to read you before my last post.
I'll try to carry out your suggestion. Thank you.

Frothingslosh · Jul 23, 2014

In answer to your question:

Please don't cross-post without providing links to your cross-posts. We answer questions for free. Please don't waste the time of the people helping you.
For cross-posting etiquette, please read: http://www.excelguru.ca/content.php?184

eduardo · Jul 23, 2014

Frothingslosh said:
In answer to your question:

Got it, thanks.

jdraw · Jul 23, 2014

The DMAx link I showed will work in your case if you find the Max sequence number for the given LoanType. It will take a little vba but nothing too complex.

As for cross posting see this link. It's more an etiquette thing than forums terms, but nobody wants to work in the dark at something if you have been getting help or have solved an issue elsewhere. A simple link to the other post(s) is sufficient. People here are volunteers who answer questions and assist in whatever way they can (often on many forums), but if you still don't see the point, then carry on.

If you need more help with vba, then a good reference is functionX

eduardo · Jul 23, 2014

jdraw said:
The DMAx link I showed will work in your case if you find the Max sequence number for the given LoanType. It will take a little vba but nothing too complex.

As for cross posting see this link. It's more an etiquette thing than forums terms, but nobody wants to work in the dark at something if you have been getting help or have solved an issue elsewhere. A simple link to the other post(s) is sufficient. People here are volunteers who answer questions and assist in whatever way they can (often on many forums), but if you still don't see the point, then carry on.

If you need more help with vba, then a good reference is functionX

Thanks for the guidance, I'm working on it, I'll post a sample once I get it done.

P.S.: I do see the point and agree, I'll remember it for future posts.

Obtaining 3 separate totals in one query (1 Viewer)

Registered User.

Attachments

bit cruncher

Registered User.

bit cruncher

Registered User.

Premier Pale Stale Ale

Super Moderator

Registered User.

Super Moderator

AWF VIP

Registered User.

Attachments

Super Moderator

Super Moderator

bit cruncher

Registered User.

Registered User.

Premier Pale Stale Ale

Registered User.

Super Moderator

Registered User.

Similar threads

Users who are viewing this thread