Query to combine records based on matching criteria. PLEASE help.

I

Ian W.

Hello all, I'm new to access and having some difficulities. Any help I could
get I would really appricate.

I have two tables from which im trying to combine records.

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:
TABLE 1
Date Zip Code Weight Cube
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

TABLE 2
Date Zip Code Weight Cube
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:
Date Zip Code Weight 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 have been succesful pulling records from one table that match another my
using relationships. However, I believe this is not pulling records from both
tables, only one; and using this method I can not sum the weight and cube
fields.

If you have any ideas or know how to do this you imput would be greatly
appricated. PLEAE help. Thanks in advance.
-Ian
 
K

Klatuu

You can do this with a Totals query and a couple of calculated fields. Join
the two tables on ZipCode. Then in your query you want:
Date ZipCode TotWeight: Nz([Table1].[Weight],0) + Nz([Table2].[Weight], 0)

And TotalCube: Nz([Table1].[Cube],0) + Nz([Table2].[Cube], 0)

Then in the Totals row:
Date - Group By
ZipCode - Group By
TotWeight - Sum
TotCube - Sum
 
K

KARL DEWEY

Try this --
SELECT [Table 1].Date, [Table 1].[Zip Code], Sum(nz([Table
1].[Weight])+nz([Table 2].[Weight])) AS [Total Weight], Sum(nz([Table
1].[Cube])+nz([Table 2].[Cube])) AS [Total Cube]
FROM [Table 1] INNER JOIN [Table 2] ON ([Table 1].[Zip Code] = [Table
2].[Zip Code]) AND ([Table 1].Date = [Table 2].Date)
GROUP BY [Table 1].Date, [Table 1].[Zip Code];
 
I

Ian W.

Hi Klatuu, thank you for you your help.
I tried this but I get the message:
"You tried to excute a query that does not include a specified expression
"Sum(LoadCons.KaneCube=Nz(LoadCons!KaneCube,0) And
Sum(LoadCons.KaneWgt)=Nz(LoadCons!KaneWgt,0)" as part of an aggregate
function.
LoadCons is one of my tables. Is there something I'm doing differently than
what you suggested? Please let me know if you have any suggestions.
Thanks Again,
-Ian


Klatuu said:
You can do this with a Totals query and a couple of calculated fields. Join
the two tables on ZipCode. Then in your query you want:
Date ZipCode TotWeight: Nz([Table1].[Weight],0) + Nz([Table2].[Weight], 0)

And TotalCube: Nz([Table1].[Cube],0) + Nz([Table2].[Cube], 0)

Then in the Totals row:
Date - Group By
ZipCode - Group By
TotWeight - Sum
TotCube - Sum




Ian W. said:
Hello all, I'm new to access and having some difficulities. Any help I could
get I would really appricate.

I have two tables from which im trying to combine records.

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:
TABLE 1
Date Zip Code Weight Cube
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

TABLE 2
Date Zip Code Weight Cube
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:
Date Zip Code Weight 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 have been succesful pulling records from one table that match another my
using relationships. However, I believe this is not pulling records from both
tables, only one; and using this method I can not sum the weight and cube
fields.

If you have any ideas or know how to do this you imput would be greatly
appricated. PLEAE help. Thanks in advance.
-Ian
Click to expand...
Click to expand...
 
I

Ian W.

Hi Karl, Thank you for your help as well.

I tried entering this SQL with my own table names and fields applied:
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].KanePUDate)
GROUP BY [LoadCons].KanePUDate, [LoadCons].[KaneZip];

My 2 tables are: "LoadCons" and "Lander"
Fields under LoadCons: KaneZip, KanePUDate, KaneCube, and KaneWgt
Fields under Lander: LanderZip, LanderPUDate, LanderCube, and LanderWgt

When I enter this SQL i get a message asking me for a parameter value for
Lander.KanePUDate. Im not sure what this means. The query only runs if I do
not enter anything.

If I try entering a value I get the message:
"This expression is typed incorrectly, or it is too complex to be evaluated.
For example, a numeric expression may contain too many complicated elements.
Try simplifying the expression by assining parts of the expression to
variables."

Any Ideas or did I enter someting wrong in the SQL?
Please let me know if you have any more suggestions.
Thanks so much again. I really appricate you help.
-Ian


KARL DEWEY said:
Try this --
SELECT [Table 1].Date, [Table 1].[Zip Code], Sum(nz([Table
1].[Weight])+nz([Table 2].[Weight])) AS [Total Weight], Sum(nz([Table
1].[Cube])+nz([Table 2].[Cube])) AS [Total Cube]
FROM [Table 1] INNER JOIN [Table 2] ON ([Table 1].[Zip Code] = [Table
2].[Zip Code]) AND ([Table 1].Date = [Table 2].Date)
GROUP BY [Table 1].Date, [Table 1].[Zip Code];


Ian W. said:
Hello all, I'm new to access and having some difficulities. Any help I could
get I would really appricate.

I have two tables from which im trying to combine records.

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:
TABLE 1
Date Zip Code Weight Cube
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

TABLE 2
Date Zip Code Weight Cube
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:
Date Zip Code Weight 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 have been succesful pulling records from one table that match another my
using relationships. However, I believe this is not pulling records from both
tables, only one; and using this method I can not sum the weight and cube
fields.

If you have any ideas or know how to do this you imput would be greatly
appricated. PLEAE help. Thanks in advance.
-Ian
Click to expand...
Click to expand...
 
K

KARL DEWEY

You have a typo - try this ---
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];


Ian W. said:
Hi Karl, Thank you for your help as well.

I tried entering this SQL with my own table names and fields applied:
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].KanePUDate)
GROUP BY [LoadCons].KanePUDate, [LoadCons].[KaneZip];

My 2 tables are: "LoadCons" and "Lander"
Fields under LoadCons: KaneZip, KanePUDate, KaneCube, and KaneWgt
Fields under Lander: LanderZip, LanderPUDate, LanderCube, and LanderWgt

When I enter this SQL i get a message asking me for a parameter value for
Lander.KanePUDate. Im not sure what this means. The query only runs if I do
not enter anything.

If I try entering a value I get the message:
"This expression is typed incorrectly, or it is too complex to be evaluated.
For example, a numeric expression may contain too many complicated elements.
Try simplifying the expression by assining parts of the expression to
variables."

Any Ideas or did I enter someting wrong in the SQL?
Please let me know if you have any more suggestions.
Thanks so much again. I really appricate you help.
-Ian


KARL DEWEY said:
Try this --
SELECT [Table 1].Date, [Table 1].[Zip Code], Sum(nz([Table
1].[Weight])+nz([Table 2].[Weight])) AS [Total Weight], Sum(nz([Table
1].[Cube])+nz([Table 2].[Cube])) AS [Total Cube]
FROM [Table 1] INNER JOIN [Table 2] ON ([Table 1].[Zip Code] = [Table
2].[Zip Code]) AND ([Table 1].Date = [Table 2].Date)
GROUP BY [Table 1].Date, [Table 1].[Zip Code];


Ian W. said:
Hello all, I'm new to access and having some difficulities. Any help I could
get I would really appricate.

I have two tables from which im trying to combine records.

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:
TABLE 1
Date Zip Code Weight Cube
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

TABLE 2
Date Zip Code Weight Cube
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:
Date Zip Code Weight 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 have been succesful pulling records from one table that match another my
using relationships. However, I believe this is not pulling records from both
tables, only one; and using this method I can not sum the weight and cube
fields.

If you have any ideas or know how to do this you imput would be greatly
appricated. PLEAE help. Thanks in advance.
-Ian
Click to expand...
Click to expand...
Click to expand...
 
I

Ian W.

Karl,
Thanks for that. The query is working now with just one problem . When there
are two records from one table (table 1) and one from another (table 2), the
cube and weight for table one totals correctly, but the numbers double for
the second table.

For example,
Table 1
zip______date_____cube____weight
00001___1/1______3________4
00001___1/1______2________1
00002___1/2______6________6

Table 2
zip______date_____cube____weight
00001___1/1______2________2
00002___1/3______4________4

The query should return
zip______date_____cube____weight
00001___1/1______7________7
(taking the first2 records from tabhle 1 and the first from table2)

Instead it is returning:
zip______date_____cube____weight
00001___1/1______9________9
(taking the first 2 records from table 1, and doubling the first record from
table 2)

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

I'm sure you busy but if you have any suggestions please let me know. I
really appricate you taking the time to help me out. Thanks again.
-Ian


KARL DEWEY said:
You have a typo - try this ---
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];


Ian W. said:
Hi Karl, Thank you for your help as well.

I tried entering this SQL with my own table names and fields applied:
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].KanePUDate)
GROUP BY [LoadCons].KanePUDate, [LoadCons].[KaneZip];

My 2 tables are: "LoadCons" and "Lander"
Fields under LoadCons: KaneZip, KanePUDate, KaneCube, and KaneWgt
Fields under Lander: LanderZip, LanderPUDate, LanderCube, and LanderWgt

When I enter this SQL i get a message asking me for a parameter value for
Lander.KanePUDate. Im not sure what this means. The query only runs if I do
not enter anything.

If I try entering a value I get the message:
"This expression is typed incorrectly, or it is too complex to be evaluated.
For example, a numeric expression may contain too many complicated elements.
Try simplifying the expression by assining parts of the expression to
variables."

Any Ideas or did I enter someting wrong in the SQL?
Please let me know if you have any more suggestions.
Thanks so much again. I really appricate you help.
-Ian


KARL DEWEY said:
Try this --
SELECT [Table 1].Date, [Table 1].[Zip Code], Sum(nz([Table
1].[Weight])+nz([Table 2].[Weight])) AS [Total Weight], Sum(nz([Table
1].[Cube])+nz([Table 2].[Cube])) AS [Total Cube]
FROM [Table 1] INNER JOIN [Table 2] ON ([Table 1].[Zip Code] = [Table
2].[Zip Code]) AND ([Table 1].Date = [Table 2].Date)
GROUP BY [Table 1].Date, [Table 1].[Zip Code];


:

Hello all, I'm new to access and having some difficulities. Any help I could
get I would really appricate.

I have two tables from which im trying to combine records.

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:
TABLE 1
Date Zip Code Weight Cube
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

TABLE 2
Date Zip Code Weight Cube
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:
Date Zip Code Weight 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 have been succesful pulling records from one table that match another my
using relationships. However, I believe this is not pulling records from both
tables, only one; and using this method I can not sum the weight and cube
fields.

If you have any ideas or know how to do this you imput would be greatly
appricated. PLEAE help. Thanks in advance.
-Ian
Click to expand...
Click to expand...
Click to expand...
 
K

KARL DEWEY

I do not have that problem. The only difference that I may have is that I am
using a true datetime datatype for the dates. But I do not see how that
would make any never mind.

Ian W. said:
Karl,
Thanks for that. The query is working now with just one problem . When there
are two records from one table (table 1) and one from another (table 2), the
cube and weight for table one totals correctly, but the numbers double for
the second table.

For example,
Table 1
zip______date_____cube____weight
00001___1/1______3________4
00001___1/1______2________1
00002___1/2______6________6

Table 2
zip______date_____cube____weight
00001___1/1______2________2
00002___1/3______4________4

The query should return
zip______date_____cube____weight
00001___1/1______7________7
(taking the first2 records from tabhle 1 and the first from table2)

Instead it is returning:
zip______date_____cube____weight
00001___1/1______9________9
(taking the first 2 records from table 1, and doubling the first record from
table 2)

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

I'm sure you busy but if you have any suggestions please let me know. I
really appricate you taking the time to help me out. Thanks again.
-Ian


KARL DEWEY said:
You have a typo - try this ---
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];


Ian W. said:
Hi Karl, Thank you for your help as well.

I tried entering this SQL with my own table names and fields applied:
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].KanePUDate)
GROUP BY [LoadCons].KanePUDate, [LoadCons].[KaneZip];

My 2 tables are: "LoadCons" and "Lander"
Fields under LoadCons: KaneZip, KanePUDate, KaneCube, and KaneWgt
Fields under Lander: LanderZip, LanderPUDate, LanderCube, and LanderWgt

When I enter this SQL i get a message asking me for a parameter value for
Lander.KanePUDate. Im not sure what this means. The query only runs if I do
not enter anything.

If I try entering a value I get the message:
"This expression is typed incorrectly, or it is too complex to be evaluated.
For example, a numeric expression may contain too many complicated elements.
Try simplifying the expression by assining parts of the expression to
variables."

Any Ideas or did I enter someting wrong in the SQL?
Please let me know if you have any more suggestions.
Thanks so much again. I really appricate you help.
-Ian


:

Try this --
SELECT [Table 1].Date, [Table 1].[Zip Code], Sum(nz([Table
1].[Weight])+nz([Table 2].[Weight])) AS [Total Weight], Sum(nz([Table
1].[Cube])+nz([Table 2].[Cube])) AS [Total Cube]
FROM [Table 1] INNER JOIN [Table 2] ON ([Table 1].[Zip Code] = [Table
2].[Zip Code]) AND ([Table 1].Date = [Table 2].Date)
GROUP BY [Table 1].Date, [Table 1].[Zip Code];


:

Hello all, I'm new to access and having some difficulities. Any help I could
get I would really appricate.

I have two tables from which im trying to combine records.

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:
TABLE 1
Date Zip Code Weight Cube
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

TABLE 2
Date Zip Code Weight Cube
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:
Date Zip Code Weight 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 have been succesful pulling records from one table that match another my
using relationships. However, I believe this is not pulling records from both
tables, only one; and using this method I can not sum the weight and cube
fields.

If you have any ideas or know how to do this you imput would be greatly
appricated. PLEAE help. Thanks in advance.
-Ian
Click to expand...
Click to expand...
Click to expand...
 
I

Ian W.

Karl,
Thanks for your reply. I've tried running the query with the dates in
DateValue format but I still get the same thing. I reimported all the data
again too and got the same thing. Maybe its something else?

Also, I'm trying to figure out a way to run the query so that it will return
exact matches on zip code but would match up dates one day behind or ahead.
I've fooled around in design view making the total column an expression. I
also tried what the help suggested to use the function "between....and..." I
then added 1 and subtracted 1 from the value of the date in date value
format. Any ideas?

Thank you so much again for your help.
-Ian

KARL DEWEY said:
I do not have that problem. The only difference that I may have is that I am
using a true datetime datatype for the dates. But I do not see how that
would make any never mind.

Ian W. said:
Karl,
Thanks for that. The query is working now with just one problem . When there
are two records from one table (table 1) and one from another (table 2), the
cube and weight for table one totals correctly, but the numbers double for
the second table.

For example,
Table 1
zip______date_____cube____weight
00001___1/1______3________4
00001___1/1______2________1
00002___1/2______6________6

Table 2
zip______date_____cube____weight
00001___1/1______2________2
00002___1/3______4________4

The query should return
zip______date_____cube____weight
00001___1/1______7________7
(taking the first2 records from tabhle 1 and the first from table2)

Instead it is returning:
zip______date_____cube____weight
00001___1/1______9________9
(taking the first 2 records from table 1, and doubling the first record from
table 2)

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

I'm sure you busy but if you have any suggestions please let me know. I
really appricate you taking the time to help me out. Thanks again.
-Ian


KARL DEWEY said:
You have a typo - try this ---
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];


:

Hi Karl, Thank you for your help as well.

I tried entering this SQL with my own table names and fields applied:
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].KanePUDate)
GROUP BY [LoadCons].KanePUDate, [LoadCons].[KaneZip];

My 2 tables are: "LoadCons" and "Lander"
Fields under LoadCons: KaneZip, KanePUDate, KaneCube, and KaneWgt
Fields under Lander: LanderZip, LanderPUDate, LanderCube, and LanderWgt

When I enter this SQL i get a message asking me for a parameter value for
Lander.KanePUDate. Im not sure what this means. The query only runs if I do
not enter anything.

If I try entering a value I get the message:
"This expression is typed incorrectly, or it is too complex to be evaluated.
For example, a numeric expression may contain too many complicated elements.
Try simplifying the expression by assining parts of the expression to
variables."

Any Ideas or did I enter someting wrong in the SQL?
Please let me know if you have any more suggestions.
Thanks so much again. I really appricate you help.
-Ian


:

Try this --
SELECT [Table 1].Date, [Table 1].[Zip Code], Sum(nz([Table
1].[Weight])+nz([Table 2].[Weight])) AS [Total Weight], Sum(nz([Table
1].[Cube])+nz([Table 2].[Cube])) AS [Total Cube]
FROM [Table 1] INNER JOIN [Table 2] ON ([Table 1].[Zip Code] = [Table
2].[Zip Code]) AND ([Table 1].Date = [Table 2].Date)
GROUP BY [Table 1].Date, [Table 1].[Zip Code];


:

Hello all, I'm new to access and having some difficulities. Any help I could
get I would really appricate.

I have two tables from which im trying to combine records.

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:
TABLE 1
Date Zip Code Weight Cube
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

TABLE 2
Date Zip Code Weight Cube
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:
Date Zip Code Weight 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 have been succesful pulling records from one table that match another my
using relationships. However, I believe this is not pulling records from both
tables, only one; and using this method I can not sum the weight and cube
fields.

If you have any ideas or know how to do this you imput would be greatly
appricated. PLEAE help. Thanks in advance.
-Ian
Click to expand...
Click to expand...
Click to expand...
 
K

KARL DEWEY

I am stumped. Maybe start a new thread with your SQL, data and results.

Ian W. said:
Karl,
Thanks for your reply. I've tried running the query with the dates in
DateValue format but I still get the same thing. I reimported all the data
again too and got the same thing. Maybe its something else?

Also, I'm trying to figure out a way to run the query so that it will return
exact matches on zip code but would match up dates one day behind or ahead.
I've fooled around in design view making the total column an expression. I
also tried what the help suggested to use the function "between....and..." I
then added 1 and subtracted 1 from the value of the date in date value
format. Any ideas?

Thank you so much again for your help.
-Ian

KARL DEWEY said:
I do not have that problem. The only difference that I may have is that I am
using a true datetime datatype for the dates. But I do not see how that
would make any never mind.

Ian W. said:
Karl,
Thanks for that. The query is working now with just one problem . When there
are two records from one table (table 1) and one from another (table 2), the
cube and weight for table one totals correctly, but the numbers double for
the second table.

For example,
Table 1
zip______date_____cube____weight
00001___1/1______3________4
00001___1/1______2________1
00002___1/2______6________6

Table 2
zip______date_____cube____weight
00001___1/1______2________2
00002___1/3______4________4

The query should return
zip______date_____cube____weight
00001___1/1______7________7
(taking the first2 records from tabhle 1 and the first from table2)

Instead it is returning:
zip______date_____cube____weight
00001___1/1______9________9
(taking the first 2 records from table 1, and doubling the first record from
table 2)

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

I'm sure you busy but if you have any suggestions please let me know. I
really appricate you taking the time to help me out. Thanks again.
-Ian


:

You have a typo - try this ---
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];


:

Hi Karl, Thank you for your help as well.

I tried entering this SQL with my own table names and fields applied:
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].KanePUDate)
GROUP BY [LoadCons].KanePUDate, [LoadCons].[KaneZip];

My 2 tables are: "LoadCons" and "Lander"
Fields under LoadCons: KaneZip, KanePUDate, KaneCube, and KaneWgt
Fields under Lander: LanderZip, LanderPUDate, LanderCube, and LanderWgt

When I enter this SQL i get a message asking me for a parameter value for
Lander.KanePUDate. Im not sure what this means. The query only runs if I do
not enter anything.

If I try entering a value I get the message:
"This expression is typed incorrectly, or it is too complex to be evaluated.
For example, a numeric expression may contain too many complicated elements.
Try simplifying the expression by assining parts of the expression to
variables."

Any Ideas or did I enter someting wrong in the SQL?
Please let me know if you have any more suggestions.
Thanks so much again. I really appricate you help.
-Ian


:

Try this --
SELECT [Table 1].Date, [Table 1].[Zip Code], Sum(nz([Table
1].[Weight])+nz([Table 2].[Weight])) AS [Total Weight], Sum(nz([Table
1].[Cube])+nz([Table 2].[Cube])) AS [Total Cube]
FROM [Table 1] INNER JOIN [Table 2] ON ([Table 1].[Zip Code] = [Table
2].[Zip Code]) AND ([Table 1].Date = [Table 2].Date)
GROUP BY [Table 1].Date, [Table 1].[Zip Code];


:

Hello all, I'm new to access and having some difficulities. Any help I could
get I would really appricate.

I have two tables from which im trying to combine records.

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:
TABLE 1
Date Zip Code Weight Cube
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

TABLE 2
Date Zip Code Weight Cube
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:
Date Zip Code Weight 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 have been succesful pulling records from one table that match another my
using relationships. However, I believe this is not pulling records from both
tables, only one; and using this method I can not sum the weight and cube
fields.

If you have any ideas or know how to do this you imput would be greatly
appricated. PLEAE help. Thanks in advance.
-Ian
Click to expand...
Click to expand...
Click to expand...
 
I

Ian W.

will do. Thanks for you help Karl, I appricate it.

KARL DEWEY said:
I am stumped. Maybe start a new thread with your SQL, data and results.

Ian W. said:
Karl,
Thanks for your reply. I've tried running the query with the dates in
DateValue format but I still get the same thing. I reimported all the data
again too and got the same thing. Maybe its something else?

Also, I'm trying to figure out a way to run the query so that it will return
exact matches on zip code but would match up dates one day behind or ahead.
I've fooled around in design view making the total column an expression. I
also tried what the help suggested to use the function "between....and..." I
then added 1 and subtracted 1 from the value of the date in date value
format. Any ideas?

Thank you so much again for your help.
-Ian

KARL DEWEY said:
I do not have that problem. The only difference that I may have is that I am
using a true datetime datatype for the dates. But I do not see how that
would make any never mind.

:

Karl,
Thanks for that. The query is working now with just one problem . When there
are two records from one table (table 1) and one from another (table 2), the
cube and weight for table one totals correctly, but the numbers double for
the second table.

For example,
Table 1
zip______date_____cube____weight
00001___1/1______3________4
00001___1/1______2________1
00002___1/2______6________6

Table 2
zip______date_____cube____weight
00001___1/1______2________2
00002___1/3______4________4

The query should return
zip______date_____cube____weight
00001___1/1______7________7
(taking the first2 records from tabhle 1 and the first from table2)

Instead it is returning:
zip______date_____cube____weight
00001___1/1______9________9
(taking the first 2 records from table 1, and doubling the first record from
table 2)

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

I'm sure you busy but if you have any suggestions please let me know. I
really appricate you taking the time to help me out. Thanks again.
-Ian


:

You have a typo - try this ---
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];


:

Hi Karl, Thank you for your help as well.

I tried entering this SQL with my own table names and fields applied:
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].KanePUDate)
GROUP BY [LoadCons].KanePUDate, [LoadCons].[KaneZip];

My 2 tables are: "LoadCons" and "Lander"
Fields under LoadCons: KaneZip, KanePUDate, KaneCube, and KaneWgt
Fields under Lander: LanderZip, LanderPUDate, LanderCube, and LanderWgt

When I enter this SQL i get a message asking me for a parameter value for
Lander.KanePUDate. Im not sure what this means. The query only runs if I do
not enter anything.

If I try entering a value I get the message:
"This expression is typed incorrectly, or it is too complex to be evaluated.
For example, a numeric expression may contain too many complicated elements.
Try simplifying the expression by assining parts of the expression to
variables."

Any Ideas or did I enter someting wrong in the SQL?
Please let me know if you have any more suggestions.
Thanks so much again. I really appricate you help.
-Ian


:

Try this --
SELECT [Table 1].Date, [Table 1].[Zip Code], Sum(nz([Table
1].[Weight])+nz([Table 2].[Weight])) AS [Total Weight], Sum(nz([Table
1].[Cube])+nz([Table 2].[Cube])) AS [Total Cube]
FROM [Table 1] INNER JOIN [Table 2] ON ([Table 1].[Zip Code] = [Table
2].[Zip Code]) AND ([Table 1].Date = [Table 2].Date)
GROUP BY [Table 1].Date, [Table 1].[Zip Code];


:

Hello all, I'm new to access and having some difficulities. Any help I could
get I would really appricate.

I have two tables from which im trying to combine records.

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:
TABLE 1
Date Zip Code Weight Cube
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

TABLE 2
Date Zip Code Weight Cube
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:
Date Zip Code Weight 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 have been succesful pulling records from one table that match another my
using relationships. However, I believe this is not pulling records from both
tables, only one; and using this method I can not sum the weight and cube
fields.

If you have any ideas or know how to do this you imput would be greatly
appricated. PLEAE help. Thanks in advance.
-Ian
Click to expand...
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

Query help 1
MATCHING RECORDS BASED ON A RANGE OF DATA 3
Query to select individual records 4
Join Query Problem for matching criteria, PLEASE help 2
linked table using SQL Query 8
Query on two tables with matching null fields 4
Count Matching records 2
Query data from 3 tables with similar data 0

Top