Join Query Problem for matching criteria, PLEASE help

G

Guest

Hello, I posted a few days ago and received help but I’m still having a
problem with the query I’m trying to run. I’m new to Access and if anyone
could help out I would really appreciate it. Please, any ideas or suggestions
would really help. Thanks.

I have two tables from which im trying to combine records: “LoadCons†and
“Landerâ€

I have 4 fields in each table.
They are: "Date", "ZipCode", "Weight", and "Cube"

I want to match on Date and Zip Code and sum at Weight and Cube for those
records that match.

For example:
If these were my tables:
LoadCons
KanePUDate ___KaneZip___KaneWgt____KaneCube
1/2___________00001______2__________3
1/2___________00002______3__________2
1/3___________00003______1__________2
1/3___________00004______2__________3
1/3___________00005______2__________1

Lander
LanderPUDate ___LanderZip___LanderWgt____LanderCube
1/2______________00006______2____________3
1/2______________00002______3____________2
1/3______________00003______1____________2
1/3______________00007______2____________3
1/3______________00008______2____________1

I would want the query to return:
KanePUDate ___KaneZip___Total Weight____Total Cube
1/2___________00002________6___________4
(combined the 2nd record from each table)
1/3___________00003________2___________4
(combined the 3nd record from each table)

I’m using this SQL which another user (Karl Dewey) helped me out with:

SELECT [LoadCons].KanePUDate, [LoadCons].[KaneZip],
Sum(nz([LoadCons].[KaneWgt])+nz([Lander].[LanderWgt])) AS [Total Weight],
Sum(nz([LoadCons].[KaneCube])+nz([Lander].[LanderCube])) AS [Total Cube]
FROM [LoadCons] INNER JOIN [Lander] ON ([LoadCons].[KaneZip] =
[Lander].[LanderZip]) AND ([LoadCons].KanePUDate = [Lander].[LanderPUDate])
GROUP BY [LoadCons].KanePUDate, [LoadCons].[KaneZip];

The problem is this:
When there are two records from LoadCons and one from Lander, the cube and
weight for LoadCons totals correctly, but the numbers double for
the Lander.

For example,
LoadCons
KanePUDate ___KaneZip___KaneWgt____KaneCube
1/1___________00001______3_________4
1/1___________00001______2_________1
1/2___________00002______6_________6

Lander
LanderPUDate ___LanderZip___LanderWgt____LanderCube
1/1______________00001_______2__________2
1/3______________00002_______4__________4

The query should return
KanePUDate ___KaneZip___Total Weight____Total Cube
1/1___________00001_______7___________7
(taking the first2 records from LoadCons and the first from Lander)

Instead it is returning:
KanePUDate ___KaneZip___Total Weight____Total Cube
1/1___________00001________9____________9
(taking the first 2 records from LoadCons, and doubling the first record from
Lander)

I thought this might be due to the "nz" function and I've tried running the
query without it but that yields no change.

Does anyone have any ideas as to why its doing this? Please, I’d really
appreciate any help or ideas anyone could offer. Thanks!
-Ian
 
K

kingston via AccessMonster.com

Do a totals query on [LoadCons] - Group by first two fields, Sum last two
fields.
Do a totals query on [Lander] - Group by first two fields, Sum last two
fields.
Then do a query on the two totals queries where you join by the first two
fields and sum the last two.
Hello, I posted a few days ago and received help but I’m still having a
problem with the query I’m trying to run. I’m new to Access and if anyone
could help out I would really appreciate it. Please, any ideas or suggestions
would really help. Thanks.

I have two tables from which im trying to combine records: “LoadCons†and
“Landerâ€

I have 4 fields in each table.
They are: "Date", "ZipCode", "Weight", and "Cube"

I want to match on Date and Zip Code and sum at Weight and Cube for those
records that match.

For example:
If these were my tables:
LoadCons
KanePUDate ___KaneZip___KaneWgt____KaneCube
1/2___________00001______2__________3
1/2___________00002______3__________2
1/3___________00003______1__________2
1/3___________00004______2__________3
1/3___________00005______2__________1

Lander
LanderPUDate ___LanderZip___LanderWgt____LanderCube
1/2______________00006______2____________3
1/2______________00002______3____________2
1/3______________00003______1____________2
1/3______________00007______2____________3
1/3______________00008______2____________1

I would want the query to return:
KanePUDate ___KaneZip___Total Weight____Total Cube
1/2___________00002________6___________4
(combined the 2nd record from each table)
1/3___________00003________2___________4
(combined the 3nd record from each table)

I’m using this SQL which another user (Karl Dewey) helped me out with:

SELECT [LoadCons].KanePUDate, [LoadCons].[KaneZip],
Sum(nz([LoadCons].[KaneWgt])+nz([Lander].[LanderWgt])) AS [Total Weight],
Sum(nz([LoadCons].[KaneCube])+nz([Lander].[LanderCube])) AS [Total Cube]
FROM [LoadCons] INNER JOIN [Lander] ON ([LoadCons].[KaneZip] =
[Lander].[LanderZip]) AND ([LoadCons].KanePUDate = [Lander].[LanderPUDate])
GROUP BY [LoadCons].KanePUDate, [LoadCons].[KaneZip];

The problem is this:
When there are two records from LoadCons and one from Lander, the cube and
weight for LoadCons totals correctly, but the numbers double for
the Lander.

For example,
LoadCons
KanePUDate ___KaneZip___KaneWgt____KaneCube
1/1___________00001______3_________4
1/1___________00001______2_________1
1/2___________00002______6_________6

Lander
LanderPUDate ___LanderZip___LanderWgt____LanderCube
1/1______________00001_______2__________2
1/3______________00002_______4__________4

The query should return
KanePUDate ___KaneZip___Total Weight____Total Cube
1/1___________00001_______7___________7
(taking the first2 records from LoadCons and the first from Lander)

Instead it is returning:
KanePUDate ___KaneZip___Total Weight____Total Cube
1/1___________00001________9____________9
(taking the first 2 records from LoadCons, and doubling the first record from
Lander)

I thought this might be due to the "nz" function and I've tried running the
query without it but that yields no change.

Does anyone have any ideas as to why its doing this? Please, I’d really
appreciate any help or ideas anyone could offer. Thanks!
-Ian
Click to expand...
 
G

Guest

Thank You! It worked. I appricate it.

kingston via AccessMonster.com said:
Do a totals query on [LoadCons] - Group by first two fields, Sum last two
fields.
Do a totals query on [Lander] - Group by first two fields, Sum last two
fields.
Then do a query on the two totals queries where you join by the first two
fields and sum the last two.
Hello, I posted a few days ago and received help but I’m still having a
problem with the query I’m trying to run. I’m new to Access and if anyone
could help out I would really appreciate it. Please, any ideas or suggestions
would really help. Thanks.

I have two tables from which im trying to combine records: “LoadCons†and
“Landerâ€

I have 4 fields in each table.
They are: "Date", "ZipCode", "Weight", and "Cube"

I want to match on Date and Zip Code and sum at Weight and Cube for those
records that match.

For example:
If these were my tables:
LoadCons
KanePUDate ___KaneZip___KaneWgt____KaneCube
1/2___________00001______2__________3
1/2___________00002______3__________2
1/3___________00003______1__________2
1/3___________00004______2__________3
1/3___________00005______2__________1

Lander
LanderPUDate ___LanderZip___LanderWgt____LanderCube
1/2______________00006______2____________3
1/2______________00002______3____________2
1/3______________00003______1____________2
1/3______________00007______2____________3
1/3______________00008______2____________1

I would want the query to return:
KanePUDate ___KaneZip___Total Weight____Total Cube
1/2___________00002________6___________4
(combined the 2nd record from each table)
1/3___________00003________2___________4
(combined the 3nd record from each table)

I’m using this SQL which another user (Karl Dewey) helped me out with:

SELECT [LoadCons].KanePUDate, [LoadCons].[KaneZip],
Sum(nz([LoadCons].[KaneWgt])+nz([Lander].[LanderWgt])) AS [Total Weight],
Sum(nz([LoadCons].[KaneCube])+nz([Lander].[LanderCube])) AS [Total Cube]
FROM [LoadCons] INNER JOIN [Lander] ON ([LoadCons].[KaneZip] =
[Lander].[LanderZip]) AND ([LoadCons].KanePUDate = [Lander].[LanderPUDate])
GROUP BY [LoadCons].KanePUDate, [LoadCons].[KaneZip];

The problem is this:
When there are two records from LoadCons and one from Lander, the cube and
weight for LoadCons totals correctly, but the numbers double for
the Lander.

For example,
LoadCons
KanePUDate ___KaneZip___KaneWgt____KaneCube
1/1___________00001______3_________4
1/1___________00001______2_________1
1/2___________00002______6_________6

Lander
LanderPUDate ___LanderZip___LanderWgt____LanderCube
1/1______________00001_______2__________2
1/3______________00002_______4__________4

The query should return
KanePUDate ___KaneZip___Total Weight____Total Cube
1/1___________00001_______7___________7
(taking the first2 records from LoadCons and the first from Lander)

Instead it is returning:
KanePUDate ___KaneZip___Total Weight____Total Cube
1/1___________00001________9____________9
(taking the first 2 records from LoadCons, and doubling the first record from
Lander)

I thought this might be due to the "nz" function and I've tried running the
query without it but that yields no change.

Does anyone have any ideas as to why its doing this? Please, I’d really
appreciate any help or ideas anyone could offer. Thanks!
-Ian
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

MATCHING RECORDS BASED ON A RANGE OF DATA 3
Query to combine records based on matching criteria. PLEASE help. 10
DSum in Query 3
Access 2003 query criteria issue.... 3
Returning zero in a query? 3
Remove zero totals in query 4
Query to Include cboSymbols or ‘*’ 4
Crosstab Query by Week with Dates as Headers 5

Top