Combining Records Based On Certain Dates- PLEASE HELP

[Guest]

Hello,
I'm somewhat new to ACCESS and still trying to learn it. I'm having a
problem with a query I'm running if you could please help me out I would be
really appricative. Im trying to combine only certain records by matching up
dates from 2 different fields. I'll call the fields "date1" and "date2". My
problem is this though, there are many records with the same date in "date1"
but with different dates in "date2". I know how to simply just combine the
records by matching the dates, but I only want to combine records that have
the earliest dates in "date2" corroesponding to the "date1" field.
Here is an example:
Date1 Date2
1: 1/5/01 1/1/01
2: 1/5/01 1/1/01
3: 1/5/01 1/2/01
4: 1/5/01 1/3/01
5: 1/5/01 1/3/01
6: 1/5/01 1/4/01
7: 1/9/01 1/5/01
8: 1/9/01 1/5/01
9: 1/9/01 1/6/01
10: 1/9/01 1/7/01
11: 1/9/01 1/7/01

How do I write a query that would only combine records 1 and 2 as well as
records 7 and 8 without combining records 4 and 5 or records 10 and 11?
I only need the earliest matching dates from "date2" that correspond to a
date in "date1" to be combined.

If anyone could help me I would really appricate it. I'm stuck on this one
and any ideas you might have would really help me out. Thanks so much.
-Ian

 

[Guest]

How do I write a query that would only combine records 1 and 2
What would the combined records look like?

 

[Guest]

Hi Karl,
I havent figured out a way to only combine records 1 and 2. I found a way of
combining 1&2, 4&5, 7&8, as well as 10&11. Only problem is I only want to
combine only the earliest matching dates from "date2" that correspond to a
date in "date1". I did this by first doing a query that took the min of
"date2" using the total section of the query then another query that took the
first date of "date2".

There are other fields which I am summing together based on the dates. as
well as one that has a unique number for each record, im using a count for
this field to see how many records were combined for each new consoldated
record.
-Ian

 

[John Spencer]

You might try the following UNTESTED SQL

SELECT DISTINCT T1.Date1, T1.Date2
FROM TheTable as T INNER JOIN
(SELECT Date1, Min(Date2) as Earliest
FROM TheTable
GROUP BY Date1) as T2
ON T1.Date1 = T2.Date1 and
T1.Date2 = T2.Earliest

UNION ALL

SELECT T1.Date1, T1.Date2
FROM TheTable as T INNER JOIN
(SELECT Date1, Min(Date2) as Earliest
FROM TheTable
GROUP BY Date1) as T2
ON T1.Date1 = T2.Date1
WHERE T1.Date2 <> T2.Earliest

 

[Guest]

John,
Thank you for your reply.

I actually have 5 fields I'm working with.
They are:
SHIPTO
TRACKING_NBR
STREET_DATE (noted as "date1")
SHIP_DATE (noted as "date2")
RU WEIGHT

before I was using a count in the total for the TRACKING_NBR field to keep
track of how many records were combined(as it is a unique # to each record).
I was also using a sum for the RU WEIGHT.

I tried the SQL script you gave me, I'm not sure if I replaced the fields
right in it. It was also giving me an error message stating "characters found
after end of SQL statement". Heres what I put in:

SELECT [Access import file].SHIPTO, [Access import file].TRACKING_NBR,
[Access import file].STREET_DATE, [Access import file].SHIP_DATE, [Access
import file].[RU WEIGHT]
FROM [Access import file];
SELECT DISTINCT T1.STREET_DATE, T1.SHIP_DATE
FROM [Access import file] as T INNER JOIN
(SELECT STREET_DATE, Min(SHIP_DATE) as Earliest
FROM [Access import file]
GROUP BY STREET_DATE) as T2
ON T1.STREET_DATE = T2.SHIP_DATE and
T1.SHIP_DATE = T2.Earliest

UNION ALL

SELECT T1.STREET_DATE, T1.SHIP_DATE
FROM [Access import file] as T INNER JOIN
(SELECT STREET_DATE, Min(SHIP_DATE) as Earliest
FROM [Access import file]
GROUP BY STREET_DATE) as T2
ON T1.STREET_DATE = T2.STREET_DATE
WHERE T1.STREET_DATE <> T2.Earliest

Was this correct? or what sould I do differently.

Thank you VERY much for trying to help me out.
-Ian

 

[Guest]

John,
sorry I frogot to remove the top Select line from the query left over from
what I was trying. This is actually what I tried:

SELECT DISTINCT T1.STREET_DATE, T1.SHIP_DATE
FROM [Access import file] as T INNER JOIN
(SELECT STREET_DATE, Min(SHIP_DATE) as Earliest
FROM [Access import file]
GROUP BY STREET_DATE) as T2
ON T1.STREET_DATE = T2.SHIP_DATE and
T1.SHIP_DATE = T2.Earliest

UNION ALL

SELECT T1.STREET_DATE, T1.SHIP_DATE
FROM [Access import file] as T INNER JOIN
(SELECT STREET_DATE, Min(SHIP_DATE) as Earliest
FROM [Access import file]
GROUP BY STREET_DATE) as T2
ON T1.STREET_DATE = T2.STREET_DATE
WHERE T1.STREET_DATE <> T2.Earliest

It actually gives me a message stating "syntax error in Join operation" when
I try to run the query.

Thanks again,
-Ian

 

[John Spencer]

The problem here is that Access SQL cannot (as far as I know) use [] in a
sub query in the FROM clause. You have spaces in your tableName and that
requires the []. If you can rename the table to AccessImportFile then you
could try using the following.

Also, the error came from your first

===========================================
SELECT DISTINCT T1.STREET_DATE, T1.SHIP_DATE
FROM AccessImportFile as T INNER JOIN
(SELECT STREET_DATE, Min(SHIP_DATE) as Earliest
FROM AccessImportFile
GROUP BY STREET_DATE) as T2
ON T1.STREET_DATE = T2.SHIP_DATE and
T1.SHIP_DATE = T2.Earliest

UNION ALL

SELECT T1.STREET_DATE, T1.SHIP_DATE
FROM AccessImportFile as T INNER JOIN
(SELECT STREET_DATE, Min(SHIP_DATE) as Earliest
FROM AccessImportFile
GROUP BY STREET_DATE) as T2
ON T1.STREET_DATE = T2.STREET_DATE
WHERE T1.STREET_DATE <> T2.Earliest
===========================================

If you can't rename the table, then create a query and save it as qEarliest
SELECT STREET_DATE, Min(SHIP_DATE) as Earliest
FROM [Access Import File]
GROUP BY STREET_DATE

Use that saved query (qEarliest) in the union query
===========================================
SELECT DISTINCT T1.STREET_DATE, T1.SHIP_DATE
FROM AccessImportFile as T INNER JOIN qEarliest as T2
ON T1.STREET_DATE = T2.SHIP_DATE and
T1.SHIP_DATE = T2.Earliest

UNION ALL

SELECT T1.STREET_DATE, T1.SHIP_DATE
FROM AccessImportFile as T INNER JOIN
qEarliest as T2
ON T1.STREET_DATE = T2.STREET_DATE
WHERE T1.STREET_DATE <> T2.Earliest
===========================================

John,
Thank you for your reply.

I actually have 5 fields I'm working with.
They are:
SHIPTO
TRACKING_NBR
STREET_DATE (noted as "date1")
SHIP_DATE (noted as "date2")
RU WEIGHT

before I was using a count in the total for the TRACKING_NBR field to keep
track of how many records were combined(as it is a unique # to each
record).
I was also using a sum for the RU WEIGHT.

I tried the SQL script you gave me, I'm not sure if I replaced the fields
right in it. It was also giving me an error message stating "characters
found
after end of SQL statement". Heres what I put in:

SELECT [Access import file].SHIPTO, [Access import file].TRACKING_NBR,
[Access import file].STREET_DATE, [Access import file].SHIP_DATE, [Access
import file].[RU WEIGHT]
FROM [Access import file];
SELECT DISTINCT T1.STREET_DATE, T1.SHIP_DATE
FROM [Access import file] as T INNER JOIN
(SELECT STREET_DATE, Min(SHIP_DATE) as Earliest
FROM [Access import file]
GROUP BY STREET_DATE) as T2
ON T1.STREET_DATE = T2.SHIP_DATE and
T1.SHIP_DATE = T2.Earliest

UNION ALL

SELECT T1.STREET_DATE, T1.SHIP_DATE
FROM [Access import file] as T INNER JOIN
(SELECT STREET_DATE, Min(SHIP_DATE) as Earliest
FROM [Access import file]
GROUP BY STREET_DATE) as T2
ON T1.STREET_DATE = T2.STREET_DATE
WHERE T1.STREET_DATE <> T2.Earliest

Was this correct? or what sould I do differently.

Thank you VERY much for trying to help me out.
-Ian