Comparing Data in Multiple Records?

[Guest]

I have a large amount of account history (300K records) in one table. There
may be multiple start and stop records for one account. I would like to
create a query that identifies accounts that have a start between a set of
dates and a stop between a set of dates. The dates are in YYYYMMDD format.
For instance, from the example table below, I would like to find the accounts
that both have a start date between 20051001 and 20051031 and a stop date
between 20060301 and 20060331. The only account meeting this criteria is
12345.

Account Number Type Effective Date
12345 Start 20051002
67891 Stop 20060101
12345 Stop 20060315
67891 Start 20051024
22222 Start 20050810
33333 Start 20060503
44444 Start 20060415
22222 Stop 20060404

I tried creating 2 queries: one that identified qualified stops and one
that identified qualified starts but could not figure out how to get the
commonalities of the two queries. I'm sure the answer is obvious...just not
to me. Thanks in advance for any help!

 

[Allen Browne]

1. Create a query, and add two copies of this table.
Access will alias the 2nd with a suffix of "_1".
In the properties box you can change the alias to something else if you
wish. I will use the name Dupe for this example.

2. In the upper pane of query design, drag [Account Number] from one table
to the other.
Access now displays a join line between these fields in the 2 tables.

3. Drag [Type] from the original table into the grid
In the Criteria row, enter:
"Start"
so the original table returns only Start values.

4 Drag [Type] from the duplicate table into the grid
In the Criteria row, enter:
"Stop"
so the duplicate table returns only Stop values.

5. Drag [Effective Date] from the original table into the grid.
In the Criteria row under this field, enter the start date range.

6. Drag [Effective Date] from the duplicate table into the grid.
In the Criteria row under this field, enter the stop date range.

The query will return those accounts that have a start record in the range,
and a stop record in the other range.

 

[Guest]

Allen -

I tried running the query as you described but the resulting "Account
Number" field came up blank (I had to add this field in addtion to your
instructions). I also received more results than what should be realistic.
I think that I may be getting a duplication of data. Any ideas?

1. Create a query, and add two copies of this table.
Access will alias the 2nd with a suffix of "_1".
In the properties box you can change the alias to something else if you
wish. I will use the name Dupe for this example.

2. In the upper pane of query design, drag [Account Number] from one table
to the other.
Access now displays a join line between these fields in the 2 tables.

3. Drag [Type] from the original table into the grid
In the Criteria row, enter:
"Start"
so the original table returns only Start values.

4 Drag [Type] from the duplicate table into the grid
In the Criteria row, enter:
"Stop"
so the duplicate table returns only Stop values.

5. Drag [Effective Date] from the original table into the grid.
In the Criteria row under this field, enter the start date range.

6. Drag [Effective Date] from the duplicate table into the grid.
In the Criteria row under this field, enter the stop date range.

The query will return those accounts that have a start record in the range,
and a stop record in the other range.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a large amount of account history (300K records) in one table.
There
may be multiple start and stop records for one account. I would like to
create a query that identifies accounts that have a start between a set of
dates and a stop between a set of dates. The dates are in YYYYMMDD
format.
For instance, from the example table below, I would like to find the
accounts
that both have a start date between 20051001 and 20051031 and a stop date
between 20060301 and 20060331. The only account meeting this criteria is
12345.

Account Number Type Effective Date
12345 Start 20051002
67891 Stop 20060101
12345 Stop 20060315
67891 Start 20051024
22222 Start 20050810
33333 Start 20060503
44444 Start 20060415
22222 Stop 20060404

I tried creating 2 queries: one that identified qualified stops and one
that identified qualified starts but could not figure out how to get the
commonalities of the two queries. I'm sure the answer is obvious...just
not
to me. Thanks in advance for any help!

 

[Allen Browne]

If the account number is returned blank, you must have some records that
contain a zero-length string (or spaces.)

Until that is sorted out, it's not worth trying to figure out what else is
duplicated.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen -

I tried running the query as you described but the resulting "Account
Number" field came up blank (I had to add this field in addtion to your
instructions). I also received more results than what should be
realistic.
I think that I may be getting a duplication of data. Any ideas?

1. Create a query, and add two copies of this table.
Access will alias the 2nd with a suffix of "_1".
In the properties box you can change the alias to something else if you
wish. I will use the name Dupe for this example.

2. In the upper pane of query design, drag [Account Number] from one
table
to the other.
Access now displays a join line between these fields in the 2 tables.

3. Drag [Type] from the original table into the grid
In the Criteria row, enter:
"Start"
so the original table returns only Start values.

4 Drag [Type] from the duplicate table into the grid
In the Criteria row, enter:
"Stop"
so the duplicate table returns only Stop values.

5. Drag [Effective Date] from the original table into the grid.
In the Criteria row under this field, enter the start date range.

6. Drag [Effective Date] from the duplicate table into the grid.
In the Criteria row under this field, enter the stop date range.

The query will return those accounts that have a start record in the
range,
and a stop record in the other range.

I have a large amount of account history (300K records) in one table.
There
may be multiple start and stop records for one account. I would like
to
create a query that identifies accounts that have a start between a set
of
dates and a stop between a set of dates. The dates are in YYYYMMDD
format.
For instance, from the example table below, I would like to find the
accounts
that both have a start date between 20051001 and 20051031 and a stop
date
between 20060301 and 20060331. The only account meeting this criteria
is
12345.

Account Number Type Effective Date
12345 Start 20051002
67891 Stop 20060101
12345 Stop 20060315
67891 Start 20051024
22222 Start 20050810
33333 Start 20060503
44444 Start 20060415
22222 Stop 20060404

I tried creating 2 queries: one that identified qualified stops and
one
that identified qualified starts but could not figure out how to get
the
commonalities of the two queries. I'm sure the answer is
obvious...just
not
to me. Thanks in advance for any help!