MATCHING RECORDS BASED ON A RANGE OF DATA

G

Guest

Hello, any help or suggestions anyone could offer me with this would be
greatly appreciated.

Right now, I have a query that matches on Date and Zip Code and sums at
Weight and Cube for those records that match.
(Thanks to Karl Dewey and Kingingston via AccessMonster.com for helping me
out with that)

I want to figure out a way to match on date for plus or minus one day and
still match exactly on zip code, while still summing weight and cube.

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

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

This is what the query currently does:
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

The query returns:
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 achieving this by running totals queries on each table first, grouping
by date and zip and summing weight and cube. I then used the make table query
to put the results into tables. I then ran a totals query where I joined the
fields on date and zip for the 2 newly created tables and summed weight and
cube.

Here is the SQL:

FOR THE LOADCONS TABLE:
SELECT LoadCons.KaneZip, LoadCons.KanePUDate, Sum(LoadCons.KaneCube) AS
SumOfKaneCube, Sum(LoadCons.KaneWgt) AS SumOfKaneWgt
FROM LoadCons
GROUP BY LoadCons.KaneZip, LoadCons.KanePUDate;

This created a new table “LoadConscomâ€

FOR THE LANDER TABLE:
SELECT Lander.LanderZip, Lander.LanderPUDate, Sum(Lander.LanderWgt) AS
SumOfLanderWgt, Sum(Lander.LanderCube) AS SumOfLanderCube
FROM Lander
GROUP BY Lander.LanderZip, Lander.LanderPUDate;

This created a new table “Landercomâ€

I THEN JOINED THE 2 NEW TABLES, “LoadConscom†and “Landercomâ€:
SELECT LoadConscom.KanePUDate, LoadConscom.KaneZip,
Sum(nz(LoadConscom.SumOfKaneWgt)+nz(Landercom.SumOfLanderWgt)) AS [Total
Weight], Sum(nz(LoadConscom.SumOfKaneCube)+nz(Landercom.SumOfLanderCube)) AS
[Total Cube]
FROM LoadConscom INNER JOIN Landercom ON
(LoadConscom.KaneZip=Landercom.LanderZip) AND
(LoadConscom.KanePUDate=Landercom.LanderPUDate)
GROUP BY LoadConscom.KanePUDate, LoadConscom.KaneZip;

Does anyone know a way I could do this same query but match dates not only
for exact day, but also on plus or minus a day? I still need zip code to
match exactly and sum the weight and cube fields for records combined.

Please post if you have any suggestions. I’ve tried fooling around with
different criteria but with no success. I’d really appreciate any help you
could offer. Thank You!
-Ian
 
K

kingston via AccessMonster.com

Can you use the result that you have with the combined weight and size to
make a query that sums across dates? IOW, keep what you have and write a
query based on it such that there is an additional calculated field:

MoreWeight = DSum("[Total Weight]","[OldQuery]","[KanePUDate] Between #" &
[KanePUDate]-1 & "# And #" & [KanePUDate]+1 &"#")

Add a similar calculated field for Size.
Hello, any help or suggestions anyone could offer me with this would be
greatly appreciated.

Right now, I have a query that matches on Date and Zip Code and sums at
Weight and Cube for those records that match.
(Thanks to Karl Dewey and Kingingston via AccessMonster.com for helping me
out with that)

I want to figure out a way to match on date for plus or minus one day and
still match exactly on zip code, while still summing weight and cube.

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

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

This is what the query currently does:
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

The query returns:
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 achieving this by running totals queries on each table first, grouping
by date and zip and summing weight and cube. I then used the make table query
to put the results into tables. I then ran a totals query where I joined the
fields on date and zip for the 2 newly created tables and summed weight and
cube.

Here is the SQL:

FOR THE LOADCONS TABLE:
SELECT LoadCons.KaneZip, LoadCons.KanePUDate, Sum(LoadCons.KaneCube) AS
SumOfKaneCube, Sum(LoadCons.KaneWgt) AS SumOfKaneWgt
FROM LoadCons
GROUP BY LoadCons.KaneZip, LoadCons.KanePUDate;

This created a new table “LoadConscomâ€

FOR THE LANDER TABLE:
SELECT Lander.LanderZip, Lander.LanderPUDate, Sum(Lander.LanderWgt) AS
SumOfLanderWgt, Sum(Lander.LanderCube) AS SumOfLanderCube
FROM Lander
GROUP BY Lander.LanderZip, Lander.LanderPUDate;

This created a new table “Landercomâ€

I THEN JOINED THE 2 NEW TABLES, “LoadConscom†and “Landercomâ€:
SELECT LoadConscom.KanePUDate, LoadConscom.KaneZip,
Sum(nz(LoadConscom.SumOfKaneWgt)+nz(Landercom.SumOfLanderWgt)) AS [Total
Weight], Sum(nz(LoadConscom.SumOfKaneCube)+nz(Landercom.SumOfLanderCube)) AS
[Total Cube]
FROM LoadConscom INNER JOIN Landercom ON
(LoadConscom.KaneZip=Landercom.LanderZip) AND
(LoadConscom.KanePUDate=Landercom.LanderPUDate)
GROUP BY LoadConscom.KanePUDate, LoadConscom.KaneZip;

Does anyone know a way I could do this same query but match dates not only
for exact day, but also on plus or minus a day? I still need zip code to
match exactly and sum the weight and cube fields for records combined.

Please post if you have any suggestions. I’ve tried fooling around with
different criteria but with no success. I’d really appreciate any help you
could offer. Thank You!
-Ian
Click to expand...
 
G

Guest

Thanks once again for your reply Kingston. I really appreciate you're help!
Thank you.

I tried what you said, however I keep getting an error message when I try to
execute the query. It asks me for a perameter value or says I have a sayntax
error. From my previous limited experience with access I know the parameter
value usually means theres something wrong w/ the sayntax. I’ve tried fooling
around with it for quite awhile, but with no success.

Here’s the expression as its entered into the “Field†field in the design
view. I also have the “Total†field as “expressionâ€:

MoreWeight =DSum("[Total Weight]","[exact matches on zip and
date]","[KanePUDate] Between #" & [KanePUDate]-1 & "# And #" & [KanePUDate]+1
& "#")

This is the SQL:

SELECT [exact matches on zip and date].KanePUDate, [exact matches on zip and
date].KaneZip, [exact matches on zip and date].[Total Weight], [exact matches
on zip and date].[Total Cube], [MoreWeight]=DSum("[Total Weight]","[exact
matches on zip and date]","[KanePUDate] Between #" & [KanePUDate]-1 & "# And
#" & [KanePUDate]+1 & "#") AS Expr1
FROM [exact matches on zip and date]
GROUP BY [exact matches on zip and date].KanePUDate, [exact matches on zip
and date].KaneZip, [exact matches on zip and date].[Total Weight], [exact
matches on zip and date].[Total Cube];

Please let me know if you have any idea what I’m doing wrong. Thanks again
so much for your help up to this point.
-Ian


kingston via AccessMonster.com said:
Can you use the result that you have with the combined weight and size to
make a query that sums across dates? IOW, keep what you have and write a
query based on it such that there is an additional calculated field:

MoreWeight = DSum("[Total Weight]","[OldQuery]","[KanePUDate] Between #" &
[KanePUDate]-1 & "# And #" & [KanePUDate]+1 &"#")

Add a similar calculated field for Size.
Hello, any help or suggestions anyone could offer me with this would be
greatly appreciated.

Right now, I have a query that matches on Date and Zip Code and sums at
Weight and Cube for those records that match.
(Thanks to Karl Dewey and Kingingston via AccessMonster.com for helping me
out with that)

I want to figure out a way to match on date for plus or minus one day and
still match exactly on zip code, while still summing weight and cube.

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

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

This is what the query currently does:
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

The query returns:
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 achieving this by running totals queries on each table first, grouping
by date and zip and summing weight and cube. I then used the make table query
to put the results into tables. I then ran a totals query where I joined the
fields on date and zip for the 2 newly created tables and summed weight and
cube.

Here is the SQL:

FOR THE LOADCONS TABLE:
SELECT LoadCons.KaneZip, LoadCons.KanePUDate, Sum(LoadCons.KaneCube) AS
SumOfKaneCube, Sum(LoadCons.KaneWgt) AS SumOfKaneWgt
FROM LoadCons
GROUP BY LoadCons.KaneZip, LoadCons.KanePUDate;

This created a new table “LoadConscomâ€

FOR THE LANDER TABLE:
SELECT Lander.LanderZip, Lander.LanderPUDate, Sum(Lander.LanderWgt) AS
SumOfLanderWgt, Sum(Lander.LanderCube) AS SumOfLanderCube
FROM Lander
GROUP BY Lander.LanderZip, Lander.LanderPUDate;

This created a new table “Landercomâ€

I THEN JOINED THE 2 NEW TABLES, “LoadConscom†and “Landercomâ€:
SELECT LoadConscom.KanePUDate, LoadConscom.KaneZip,
Sum(nz(LoadConscom.SumOfKaneWgt)+nz(Landercom.SumOfLanderWgt)) AS [Total
Weight], Sum(nz(LoadConscom.SumOfKaneCube)+nz(Landercom.SumOfLanderCube)) AS
[Total Cube]
FROM LoadConscom INNER JOIN Landercom ON
(LoadConscom.KaneZip=Landercom.LanderZip) AND
(LoadConscom.KanePUDate=Landercom.LanderPUDate)
GROUP BY LoadConscom.KanePUDate, LoadConscom.KaneZip;

Does anyone know a way I could do this same query but match dates not only
for exact day, but also on plus or minus a day? I still need zip code to
match exactly and sum the weight and cube fields for records combined.

Please post if you have any suggestions. I’ve tried fooling around with
different criteria but with no success. I’d really appreciate any help you
could offer. Thank You!
-Ian
Click to expand...
Click to expand...
 
K

kingston via AccessMonster.com

Try this (reformat if necessary, the main thing I saw was you don't assign a
value to a field in an SQL statement with an equal sign):

SELECT [exact matches on zip and date].KanePUDate, [exact matches on zip and
date].KaneZip, [exact matches on zip and date].[Total Weight], [exact matches

on zip and date].[Total Cube], DSum("[Total Weight]","[exact
matches on zip and date]","[KanePUDate] Between #" & [KanePUDate]-1 & "# And
#" & [KanePUDate]+1 & "#") AS MoreWeight
FROM [exact matches on zip and date]
GROUP BY [exact matches on zip and date].KanePUDate, [exact matches on zip
and date].KaneZip, [exact matches on zip and date].[Total Weight], [exact
matches on zip and date].[Total Cube];

Also, if you're using a single datasource, you don't need to add the table
qualifier in front of the field names: KanePUDate is sufficient, [exact
matches on zip and date].KanePUDate is too much typing for me.
Thanks once again for your reply Kingston. I really appreciate you're help!
Thank you.

I tried what you said, however I keep getting an error message when I try to
execute the query. It asks me for a perameter value or says I have a sayntax
error. From my previous limited experience with access I know the parameter
value usually means theres something wrong w/ the sayntax. I’ve tried fooling
around with it for quite awhile, but with no success.

Here’s the expression as its entered into the “Field†field in the design
view. I also have the “Total†field as “expressionâ€:

MoreWeight =DSum("[Total Weight]","[exact matches on zip and
date]","[KanePUDate] Between #" & [KanePUDate]-1 & "# And #" & [KanePUDate]+1
& "#")

This is the SQL:

SELECT [exact matches on zip and date].KanePUDate, [exact matches on zip and
date].KaneZip, [exact matches on zip and date].[Total Weight], [exact matches
on zip and date].[Total Cube], [MoreWeight]=DSum("[Total Weight]","[exact
matches on zip and date]","[KanePUDate] Between #" & [KanePUDate]-1 & "# And
#" & [KanePUDate]+1 & "#") AS Expr1
FROM [exact matches on zip and date]
GROUP BY [exact matches on zip and date].KanePUDate, [exact matches on zip
and date].KaneZip, [exact matches on zip and date].[Total Weight], [exact
matches on zip and date].[Total Cube];

Please let me know if you have any idea what I’m doing wrong. Thanks again
so much for your help up to this point.
-Ian
Can you use the result that you have with the combined weight and size to
make a query that sums across dates? IOW, keep what you have and write a
Click to expand...
[quoted text clipped - 89 lines]
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

Join Query Problem for matching criteria, PLEASE help 2
Query to combine records based on matching criteria. PLEASE help. 10
How to populate a column in a row based on a value in another colu 3
DSum in Query 3
Help creating query in Access or should I be using a formula in Ex 3
Subtraction Query 2
Running an update query based on aggregate result of a select quer 4
excluding records based on a table 3

Top