Compare records against each other

[PizzaBoy]

Hello Group,

I have a table with [customer], [address], and [ID] which is the primary
key. I have a second table linked one to many with the main table by [ID]
and has the fields: [rental unit], [rental date]. I am trying to create a
query which will return the results for customers who have stayed in any
unit for a specific date. The records would have to be compared among
themselves for each primary key. See example

table1![ID]=123 table1![customer]=john smith
table2![id]=123 table2![rental date]=1/1/05
table2![id]=123 table2![rental date]=2/5/03
table2![id]=123 table2![rental date]=5/3/04

I would like the query to return the records where a customer (in this
case-john smith) stayed in a rental before 2/1/05 and after 1/1/04. I only
want the name and address to appear once rather than each time there is a
match in the second table-this way I can create a make table query which
will can be used for a mailing list.

Thank you.

 

[MGFoster]

Hello Group,

I have a table with [customer], [address], and [ID] which is the primary
key. I have a second table linked one to many with the main table by [ID]
and has the fields: [rental unit], [rental date]. I am trying to create a
query which will return the results for customers who have stayed in any
unit for a specific date. The records would have to be compared among
themselves for each primary key. See example

table1![ID]=123 table1![customer]=john smith
table2![id]=123 table2![rental date]=1/1/05
table2![id]=123 table2![rental date]=2/5/03
table2![id]=123 table2![rental date]=5/3/04

I would like the query to return the records where a customer (in this
case-john smith) stayed in a rental before 2/1/05 and after 1/1/04. I only
want the name and address to appear once rather than each time there is a
match in the second table-this way I can create a make table query which
will can be used for a mailing list.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

This might work:

SELECT DISTINCTROW t1.customer, t1.address
FROM table1 as t1 INNER JOIN table2 as t2
ON t1.ID = t2.ID
WHERE t2.[rental date] BETWEEN #1/1/04# And #1/31/05#

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQifCoYechKqOuFEgEQIrNACfWRE4o02je3lIyX7oiE9I8oClXngAoNrE
YojJI0YP/aMf8k649m0V2YRv
=n9P8
-----END PGP SIGNATURE-----

 

[PizzaBoy]

Thank you for the information. I am new at Access and do not quite
understand your reply. Would you be so kind as to elaborate-is that the
setup for a query in the builder or a SQL query? Thank you.

Hello Group,

I have a table with [customer], [address], and [ID] which is the primary
key. I have a second table linked one to many with the main table by
[ID] and has the fields: [rental unit], [rental date]. I am trying to
create a query which will return the results for customers who have
stayed in any unit for a specific date. The records would have to be
compared among themselves for each primary key. See example

table1![ID]=123 table1![customer]=john smith
table2![id]=123 table2![rental date]=1/1/05
table2![id]=123 table2![rental date]=2/5/03
table2![id]=123 table2![rental date]=5/3/04

I would like the query to return the records where a customer (in this
case-john smith) stayed in a rental before 2/1/05 and after 1/1/04. I
only want the name and address to appear once rather than each time there
is a match in the second table-this way I can create a make table query
which will can be used for a mailing list.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

This might work:

SELECT DISTINCTROW t1.customer, t1.address
FROM table1 as t1 INNER JOIN table2 as t2
ON t1.ID = t2.ID
WHERE t2.[rental date] BETWEEN #1/1/04# And #1/31/05#

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQifCoYechKqOuFEgEQIrNACfWRE4o02je3lIyX7oiE9I8oClXngAoNrE
YojJI0YP/aMf8k649m0V2YRv
=n9P8
-----END PGP SIGNATURE-----

 

[MGFoster]

That is an general example that would go into the SQL view of a query.
You'd have to replace all unreal column and table names w/ your column &
table names.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

Thank you for the information. I am new at Access and do not quite
understand your reply. Would you be so kind as to elaborate-is that the
setup for a query in the builder or a SQL query? Thank you.

Hello Group,

I have a table with [customer], [address], and [ID] which is the primary
key. I have a second table linked one to many with the main table by
[ID] and has the fields: [rental unit], [rental date]. I am trying to
create a query which will return the results for customers who have
stayed in any unit for a specific date. The records would have to be
compared among themselves for each primary key. See example

table1![ID]=123 table1![customer]=john smith
table2![id]=123 table2![rental date]=1/1/05
table2![id]=123 table2![rental date]=2/5/03
table2![id]=123 table2![rental date]=5/3/04

I would like the query to return the records where a customer (in this
case-john smith) stayed in a rental before 2/1/05 and after 1/1/04. I
only want the name and address to appear once rather than each time there
is a match in the second table-this way I can create a make table query
which will can be used for a mailing list.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

This might work:

SELECT DISTINCTROW t1.customer, t1.address
FROM table1 as t1 INNER JOIN table2 as t2
ON t1.ID = t2.ID
WHERE t2.[rental date] BETWEEN #1/1/04# And #1/31/05#

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQifCoYechKqOuFEgEQIrNACfWRE4o02je3lIyX7oiE9I8oClXngAoNrE
YojJI0YP/aMf8k649m0V2YRv
=n9P8
-----END PGP SIGNATURE-----

 

[PizzaBoy]

Thank you again for the information. I should clarify that the result I am
looking for is where the records have been compared amongst themselves.
Using the example data I would like to show customers who stayed in a unit
in 2003 but not 2004. If they stayed in a unit both years it would exclude
them-which using the sample data John Smith would be excluded. Would the
sample SQL accomplish this result or would it return all customer who fall
within the selected dates regardless of any other dates.

Any helps is greatly appreciated. Thank you.

That is an general example that would go into the SQL view of a query.
You'd have to replace all unreal column and table names w/ your column &
table names.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

Thank you for the information. I am new at Access and do not quite
understand your reply. Would you be so kind as to elaborate-is that the
setup for a query in the builder or a SQL query? Thank you.

PizzaBoy wrote:

Hello Group,

I have a table with [customer], [address], and [ID] which is the primary
key. I have a second table linked one to many with the main table by
[ID] and has the fields: [rental unit], [rental date]. I am trying to
create a query which will return the results for customers who have
stayed in any unit for a specific date. The records would have to be
compared among themselves for each primary key. See example

table1![ID]=123 table1![customer]=john smith
table2![id]=123 table2![rental date]=1/1/05
table2![id]=123 table2![rental date]=2/5/03
table2![id]=123 table2![rental date]=5/3/04

I would like the query to return the records where a customer (in this
case-john smith) stayed in a rental before 2/1/05 and after 1/1/04. I
only want the name and address to appear once rather than each time
there is a match in the second table-this way I can create a make table
query which will can be used for a mailing list.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

This might work:

SELECT DISTINCTROW t1.customer, t1.address
FROM table1 as t1 INNER JOIN table2 as t2
ON t1.ID = t2.ID
WHERE t2.[rental date] BETWEEN #1/1/04# And #1/31/05#

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQifCoYechKqOuFEgEQIrNACfWRE4o02je3lIyX7oiE9I8oClXngAoNrE
YojJI0YP/aMf8k649m0V2YRv
=n9P8
-----END PGP SIGNATURE-----

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

It would return all customers who fall within the dates in the WHERE
clause.

If you want "customers who stayed in a unit in 2003, but not 2004,"
perhaps this would do:

SELECT DISTINCTROW t1.customer, t1.address
FROM table1 as t1 INNER JOIN table2 as t2
ON t1.ID = t2.ID
WHERE t2.[rental date] BETWEEN #1/1/03# And #12/31/03#
AND NOT EXISTS (SELECT * FROM table2
WHERE ID = t2.ID
AND [rental date] BETWEEN #1/1/04# And #12/31/03#)

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQijnS4echKqOuFEgEQJNdQCgjPfmHOhyYqY3DZa5c6m6gI3Zf3UAoJkd
WL1UgzQNQ6VCBiGt7aYZ161/
=GNck
-----END PGP SIGNATURE-----

Thank you again for the information. I should clarify that the result I am
looking for is where the records have been compared amongst themselves.
Using the example data I would like to show customers who stayed in a unit
in 2003 but not 2004. If they stayed in a unit both years it would exclude
them-which using the sample data John Smith would be excluded. Would the
sample SQL accomplish this result or would it return all customer who fall
within the selected dates regardless of any other dates.

Any helps is greatly appreciated. Thank you.

That is an general example that would go into the SQL view of a query.
You'd have to replace all unreal column and table names w/ your column &
table names.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

Thank you for the information. I am new at Access and do not quite
understand your reply. Would you be so kind as to elaborate-is that the
setup for a query in the builder or a SQL query? Thank you.




PizzaBoy wrote:


Hello Group,

I have a table with [customer], [address], and [ID] which is the primary
key. I have a second table linked one to many with the main table by
[ID] and has the fields: [rental unit], [rental date]. I am trying to
create a query which will return the results for customers who have
stayed in any unit for a specific date. The records would have to be
compared among themselves for each primary key. See example

table1![ID]=123 table1![customer]=john smith
table2![id]=123 table2![rental date]=1/1/05
table2![id]=123 table2![rental date]=2/5/03
table2![id]=123 table2![rental date]=5/3/04

I would like the query to return the records where a customer (in this
case-john smith) stayed in a rental before 2/1/05 and after 1/1/04. I
only want the name and address to appear once rather than each time
there is a match in the second table-this way I can create a make table
query which will can be used for a mailing list.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

This might work:

SELECT DISTINCTROW t1.customer, t1.address

FROM table1 as t1 INNER JOIN table2 as t2

ON t1.ID = t2.ID
WHERE t2.[rental date] BETWEEN #1/1/04# And #1/31/05#

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQifCoYechKqOuFEgEQIrNACfWRE4o02je3lIyX7oiE9I8oClXngAoNrE
YojJI0YP/aMf8k649m0V2YRv
=n9P8
-----END PGP SIGNATURE-----

 

[PizzaBoy]

I'll give it a try-thank you.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

It would return all customers who fall within the dates in the WHERE
clause.

If you want "customers who stayed in a unit in 2003, but not 2004,"
perhaps this would do:

SELECT DISTINCTROW t1.customer, t1.address
FROM table1 as t1 INNER JOIN table2 as t2
ON t1.ID = t2.ID
WHERE t2.[rental date] BETWEEN #1/1/03# And #12/31/03#
AND NOT EXISTS (SELECT * FROM table2
WHERE ID = t2.ID
AND [rental date] BETWEEN #1/1/04# And #12/31/03#)

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQijnS4echKqOuFEgEQJNdQCgjPfmHOhyYqY3DZa5c6m6gI3Zf3UAoJkd
WL1UgzQNQ6VCBiGt7aYZ161/
=GNck
-----END PGP SIGNATURE-----

Thank you again for the information. I should clarify that the result I
am looking for is where the records have been compared amongst
themselves. Using the example data I would like to show customers who
stayed in a unit in 2003 but not 2004. If they stayed in a unit both
years it would exclude them-which using the sample data John Smith would
be excluded. Would the sample SQL accomplish this result or would it
return all customer who fall within the selected dates regardless of any
other dates.

Any helps is greatly appreciated. Thank you.

That is an general example that would go into the SQL view of a query.
You'd have to replace all unreal column and table names w/ your column &
table names.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

PizzaBoy wrote:

Thank you for the information. I am new at Access and do not quite
understand your reply. Would you be so kind as to elaborate-is that the
setup for a query in the builder or a SQL query? Thank you.




PizzaBoy wrote:


Hello Group,

I have a table with [customer], [address], and [ID] which is the
primary key. I have a second table linked one to many with the main
table by [ID] and has the fields: [rental unit], [rental date]. I am
trying to create a query which will return the results for customers
who have stayed in any unit for a specific date. The records would
have to be compared among themselves for each primary key. See example

table1![ID]=123 table1![customer]=john smith
table2![id]=123 table2![rental date]=1/1/05
table2![id]=123 table2![rental date]=2/5/03
table2![id]=123 table2![rental date]=5/3/04

I would like the query to return the records where a customer (in this
case-john smith) stayed in a rental before 2/1/05 and after 1/1/04. I
only want the name and address to appear once rather than each time
there is a match in the second table-this way I can create a make
table query which will can be used for a mailing list.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

This might work:

SELECT DISTINCTROW t1.customer, t1.address

FROM table1 as t1 INNER JOIN table2 as t2

ON t1.ID = t2.ID
WHERE t2.[rental date] BETWEEN #1/1/04# And #1/31/05#

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQifCoYechKqOuFEgEQIrNACfWRE4o02je3lIyX7oiE9I8oClXngAoNrE
YojJI0YP/aMf8k649m0V2YRv
=n9P8
-----END PGP SIGNATURE-----