Linking Same Tables (Current/Previous) in a Query / Sum is multiplying value results

[Ben]

I'm trying to link two tables in Access...both tables contain the same
fields with YTD totals. I need to subtract YTD Total between the two,
to determine the difference. I have the tables linked via 2 fields.
I group by the two fields, and sum on YTD. It is multiplying the
values and I'm ending up with numbers in the 200,000 instead of max of
about 5,000.

I've tried doing queries separately, doing the sum, and then linking
the two new queries...only to have the same results.

I've tried turning them into Make-Table Queries, creating the tables
and running the new query off of the 2 tables where the values have
already been summed. Same results.

In one off moment...it worked correctly. The next time I went into
the Query again though, the numbers were off again. I cannot figure
out what I did differently.

What am I doing wrong? How do I achieve the correct results where the
sum value is not multipled?

Thanks

 

[John W. Vinson]

What am I doing wrong?

I don't know; what are you doing?

Please post the SQL view of your queries. Sounds like your Joins are
nonexistant or incorrect, but there's no way to tell from this distance!

John W. Vinson [MVP]

 

[Ben]

I don't know; what are you doing?

Please post the SQL view of your queries. Sounds like your Joins are
nonexistant or incorrect, but there's no way to tell from this distance!

John W. Vinson [MVP]

Below is a simple SQL being created. The two tables are linked by 2
like fields, and I've only selected 2 fields from 1 of the tables to
query by. One field is group by, the other is sum. Again, it's
multiplying the values.

SELECT [CMPM Summary Current].PMT, Sum([CMPM Summary Current].[Current
YTD]) AS [SumOfCurrent YTD]
FROM [CMPM Summary Current] LEFT JOIN [CMPM Summary Previous] ON
([CMPM Summary Current].[PMT Type] = [CMPM Summary Previous].[PMT
Type]) AND ([CMPM Summary Current].PMT = [CMPM Summary Previous].PMT)
GROUP BY [CMPM Summary Current].PMT;

 

[Ben]

I don't know; what are you doing?

Please post the SQL view of your queries. Sounds like your Joins are
nonexistant or incorrect, but there's no way to tell from this distance!

John W. Vinson [MVP]

Below is a simple SQL being created. The two tables are linked by 2
like fields, and I've only selected 2 fields from 1 of the tables to
query by. One field is group by, the other is sum. Again, it's
multiplying the values.

SELECT [CMPM Summary Current].PMT, Sum([CMPM Summary Current].[Current
YTD]) AS [SumOfCurrent YTD]
FROM [CMPM Summary Current] LEFT JOIN [CMPM Summary Previous] ON
([CMPM Summary Current].[PMT Type] = [CMPM Summary Previous].[PMT
Type]) AND ([CMPM Summary Current].PMT = [CMPM Summary Previous].PMT)
GROUP BY [CMPM Summary Current].PMT;

FYI as example....If I sum only one table, one of the PMT fields will
only have YTD of about 3,000. With the join and group by /
sum.....that same field is returning over 700,000.

 

[John W. Vinson]

Below is a simple SQL being created. The two tables are linked by 2
like fields, and I've only selected 2 fields from 1 of the tables to
query by. One field is group by, the other is sum. Again, it's
multiplying the values.

SELECT [CMPM Summary Current].PMT, Sum([CMPM Summary Current].[Current
YTD]) AS [SumOfCurrent YTD]
FROM [CMPM Summary Current] LEFT JOIN [CMPM Summary Previous] ON
([CMPM Summary Current].[PMT Type] = [CMPM Summary Previous].[PMT
Type]) AND ([CMPM Summary Current].PMT = [CMPM Summary Previous].PMT)
GROUP BY [CMPM Summary Current].PMT;

Do [PMT Type] and [PMT] between them uniquely identify a single record in
[CMPM Summary Current]? What's the Primary Key of this table? How (if at all)
are the tables related?

I'm just thinking if the fieldnames mean what they seem to mean, then you
could be linking a record with Pmt Type "Cash" and Pmt $10.00 to lots and lots
of records in [CMPM Summary Previous]...

John W. Vinson [MVP]

 

[Ben]

Below is a simple SQL being created. The two tables are linked by 2
like fields, and I've only selected 2 fields from 1 of the tables to
query by. One field is group by, the other is sum. Again, it's
multiplying the values.

SELECT [CMPM Summary Current].PMT, Sum([CMPM Summary Current].[Current
YTD]) AS [SumOfCurrent YTD]
FROM [CMPM Summary Current] LEFT JOIN [CMPM Summary Previous] ON
([CMPM Summary Current].[PMT Type] = [CMPM Summary Previous].[PMT
Type]) AND ([CMPM Summary Current].PMT = [CMPM Summary Previous].PMT)
GROUP BY [CMPM Summary Current].PMT;

Do [PMT Type] and [PMT] between them uniquely identify a single record in
[CMPM Summary Current]? What's the Primary Key of this table? How (if at all)
are the tables related?

I'm just thinking if the fieldnames mean what they seem to mean, then you
could be linking a record with Pmt Type "Cash" and Pmt $10.00 to lots and lots
of records in [CMPM Summary Previous]...

John W. Vinson [MVP]

These are Project Codes - PMT = "12345 - Misc Project One". PMT Type
= 'Testing' or 'Project Managment' or 'Planning'. This is our
accounting system where users enter their time by PMT Code and PMT
Type. I'm trying to determine 'Actual Hours' entered per week...if
you can believe our system does not give us that. So I'm trying to
work with YTD from week 1, and YTD from week 2....subtract them to
find the difference. The current table should contain ALL pmt's YTD,
and the previous table contains all PMT's but only as of the previous
week. So the link between the two should capture ALL PMT's from
Current, and all PMT's from previous where they match the
current...and they all should match because it's a YTD view. I just
can't seem to be able to subtract the year to date between the two
tables to come up with a difference.

Doesn't make much sense...it seem like it should be so simple.

 

[John W. Vinson]

These are Project Codes - PMT = "12345 - Misc Project One". PMT Type
= 'Testing' or 'Project Managment' or 'Planning'. This is our
accounting system where users enter their time by PMT Code and PMT
Type.

Again:

How many records are there in each table with a given value of the combination
of [PMT] and [PMT Type]?

If there is only one record for each such combination, you're ok.

If there are multiple records for each such combination, you *will* get
multiple records - each unique value in the first table will be joined to
every one of the matching records in the second table. Thus if there are 10
records for the combination "12345 - Misc Project One" and "Planning" in each
table you will get all 100 possible combinations.

Is there a Primary Key in either table? Or some UNIQUE value or combination of
values?

Just the fact that you have a new table each week is very disquieting. How big
are these tables?

John W. Vinson [MVP]

 

[Ben]

These are Project Codes - PMT = "12345 - Misc Project One". PMT Type
= 'Testing' or 'Project Managment' or 'Planning'. This is our
accounting system where users enter their time by PMT Code and PMT
Type.

Again:

How many records are there in each table with a given value of the combination
of [PMT] and [PMT Type]?

If there is only one record for each such combination, you're ok.

If there are multiple records for each such combination, you *will* get
multiple records - each unique value in the first table will be joined to
every one of the matching records in the second table. Thus if there are 10
records for the combination "12345 - Misc Project One" and "Planning" in each
table you will get all 100 possible combinations.

Is there a Primary Key in either table? Or some UNIQUE value or combination of
values?

Just the fact that you have a new table each week is very disquieting. How big
are these tables?

John W. Vinson [MVP]

John....thank you. I understand now what you mean. I don't much
understand the Primary Key thing....but I do understand Unique
Records. We have about 60 testers...each can assign time to different
PMT's and within that, to different PMT Types. So between the two
fields I don't have a unique recored. But based on what you just told
me, I kept my Make-Table summarizing Previous and a different Make-
Table for Current. In both, I added a concatination, combining Name,
PMT, and PMT Type....which created the unique record I needed. It's
working now...a big thanks!!!!