Union Query

[Royce Schnepp]

Here is my dilema if you will. I am working on a database for foreclosure
sales, in the process of appeding information from DBF files to tables, the
primary key (T_S_NO) in some instances is left blank, meaning it wont get
added to the database.

What i did was create two tables, one with the primary key (Master Database)
for T_S_NO and one without a primary key (T_S_NO Null) that only contains the
information that has null values. What i was trying to do is create a union
query that goes to both these fields and grabs the information relating to a
specfic sale date, so my SQL statement became this.

Select * From [Master Database] WHERE [Master Database].SALE_DATE<=[Select
Date]
UNION ALL Select * From [T_S_NO Null] Where [T_S_NO Null].SALE_DATE<=[Select
Date];


However, it asks for the sale date input twice. Is there a way to eliminate
this from happening, or does anyone have a better idea as to how to do this?

Thanks!

 

[KARL DEWEY]

it asks for the sale date input twice.
You are showing SALE_DATE as a field in both table therefore it should not
prompt for that information. If [Select Date] is not a field then it would
prompt for that.

Maybe post some sample data from both tables.

 

[Royce Schnepp]

How would i post data?

You are showing SALE_DATE as a field in both table therefore it should not
prompt for that information. If [Select Date] is not a field then it would
prompt for that.

Maybe post some sample data from both tables.

--
KARL DEWEY
Build a little - Test a little

Here is my dilema if you will. I am working on a database for foreclosure
sales, in the process of appeding information from DBF files to tables, the
primary key (T_S_NO) in some instances is left blank, meaning it wont get
added to the database.

What i did was create two tables, one with the primary key (Master Database)
for T_S_NO and one without a primary key (T_S_NO Null) that only contains the
information that has null values. What i was trying to do is create a union
query that goes to both these fields and grabs the information relating to a
specfic sale date, so my SQL statement became this.

Select * From [Master Database] WHERE [Master Database].SALE_DATE<=[Select
Date]
UNION ALL Select * From [T_S_NO Null] Where [T_S_NO Null].SALE_DATE<=[Select
Date];


However, it asks for the sale date input twice. Is there a way to eliminate
this from happening, or does anyone have a better idea as to how to do this?

Thanks!

 

[KARL DEWEY]

Run a select query without criteria, highlight a bunch of rows, copy, and
paste in a post.
--
KARL DEWEY
Build a little - Test a little

How would i post data?

it asks for the sale date input twice.

You are showing SALE_DATE as a field in both table therefore it should not
prompt for that information. If [Select Date] is not a field then it would
prompt for that.

Maybe post some sample data from both tables.

--
KARL DEWEY
Build a little - Test a little

Here is my dilema if you will. I am working on a database for foreclosure
sales, in the process of appeding information from DBF files to tables, the
primary key (T_S_NO) in some instances is left blank, meaning it wont get
added to the database.

What i did was create two tables, one with the primary key (Master Database)
for T_S_NO and one without a primary key (T_S_NO Null) that only contains the
information that has null values. What i was trying to do is create a union
query that goes to both these fields and grabs the information relating to a
specfic sale date, so my SQL statement became this.

Select * From [Master Database] WHERE [Master Database].SALE_DATE<=[Select
Date]
UNION ALL Select * From [T_S_NO Null] Where [T_S_NO Null].SALE_DATE<=[Select
Date];


However, it asks for the sale date input twice. Is there a way to eliminate
this from happening, or does anyone have a better idea as to how to do this?

Thanks!

 

[Royce Schnepp]

T_S_NO LST ADDRESS CITY ZIP TG HOEX TRUSTOR OWNER TRUSTEE T_PHONE BENEFRY B_PHONE SALE_DATE SALE_TIME SITE TAX_VALUE TX_YR PRCHS_DATE AMOUNT USE YRBLT SQFT STORY ROOMS LOT LEGAL ASSPAR NOD LOAN NTS TDID REMARKS COUNTY L_DATE
CA-08-146059-PJ 1620 S Pomona Ave C Fullerton 92832 768-H2 Y Frank T.
Carrasco Same Www.priorityposting.com 714 573-1965 Quality Loan 619
645-7711 7/16/2008 10:00 401 E. Chapman Ave.,
Placentia 160949 3 2/6/2003 234,692 CONDO 63 881 4-1-1.0 Tr 8027 Lot 3 Unit
12D1 933-01-103 08-134648 1002630040 08-308897 05-568328 1 7/10/2008
CA-08-145053-SH 801 Stardust Dr Placentia 92870 739-D6 - Jesus
Guerra Same Www.fidelityasap.com 714 259-7850 Quality Loan 619
645-7711 7/16/2008 02:00 Courthouse, 700 Civic Center Dr., Santa
Ana 161068 92 4/15/1991 491,625 R 59 1,188 6-3-2.0 N Tr 3081 Lot
31 339-312-15 08-128734 1000310503 08-308898 06-126820 1 7/10/2008

So that didnt turn out as well as i had hoped...all the bolded stuff are the
columns.

Run a select query without criteria, highlight a bunch of rows, copy, and
paste in a post.
--
KARL DEWEY
Build a little - Test a little

How would i post data?

it asks for the sale date input twice.
You are showing SALE_DATE as a field in both table therefore it should not
prompt for that information. If [Select Date] is not a field then it would
prompt for that.

Maybe post some sample data from both tables.

--
KARL DEWEY
Build a little - Test a little


:

Here is my dilema if you will. I am working on a database for foreclosure
sales, in the process of appeding information from DBF files to tables, the
primary key (T_S_NO) in some instances is left blank, meaning it wont get
added to the database.

What i did was create two tables, one with the primary key (Master Database)
for T_S_NO and one without a primary key (T_S_NO Null) that only contains the
information that has null values. What i was trying to do is create a union
query that goes to both these fields and grabs the information relating to a
specfic sale date, so my SQL statement became this.

Select * From [Master Database] WHERE [Master Database].SALE_DATE<=[Select
Date]
UNION ALL Select * From [T_S_NO Null] Where [T_S_NO Null].SALE_DATE<=[Select
Date];


However, it asks for the sale date input twice. Is there a way to eliminate
this from happening, or does anyone have a better idea as to how to do this?

Thanks!

 

[KARL DEWEY]

However, it asks for the sale date input twice
I do not see why it would ask for SALE_DATE twice.

Ok, where does [Select Date] come from? Is it a prompt? If that is what
is being asked for twice then you can use a form to input the date in a text
box and then reference the form text box in the query like this --

CVDate([Forms]![YourFormName]![selectDateTextBox])

--
KARL DEWEY
Build a little - Test a little

T_S_NO LST ADDRESS CITY ZIP TG HOEX TRUSTOR OWNER TRUSTEE T_PHONE BENEFRY B_PHONE SALE_DATE SALE_TIME SITE TAX_VALUE TX_YR PRCHS_DATE AMOUNT USE YRBLT SQFT STORY ROOMS LOT LEGAL ASSPAR NOD LOAN NTS TDID REMARKS COUNTY L_DATE
CA-08-146059-PJ 1620 S Pomona Ave C Fullerton 92832 768-H2 Y Frank T.
Carrasco Same Www.priorityposting.com 714 573-1965 Quality Loan 619
645-7711 7/16/2008 10:00 401 E. Chapman Ave.,
Placentia 160949 3 2/6/2003 234,692 CONDO 63 881 4-1-1.0 Tr 8027 Lot 3 Unit
12D1 933-01-103 08-134648 1002630040 08-308897 05-568328 1 7/10/2008
CA-08-145053-SH 801 Stardust Dr Placentia 92870 739-D6 - Jesus
Guerra Same Www.fidelityasap.com 714 259-7850 Quality Loan 619
645-7711 7/16/2008 02:00 Courthouse, 700 Civic Center Dr., Santa
Ana 161068 92 4/15/1991 491,625 R 59 1,188 6-3-2.0 N Tr 3081 Lot
31 339-312-15 08-128734 1000310503 08-308898 06-126820 1 7/10/2008

So that didnt turn out as well as i had hoped...all the bolded stuff are the
columns.

Run a select query without criteria, highlight a bunch of rows, copy, and
paste in a post.
--
KARL DEWEY
Build a little - Test a little

How would i post data?

:

it asks for the sale date input twice.
You are showing SALE_DATE as a field in both table therefore it should not
prompt for that information. If [Select Date] is not a field then it would
prompt for that.

Maybe post some sample data from both tables.

--
KARL DEWEY
Build a little - Test a little


:

Here is my dilema if you will. I am working on a database for foreclosure
sales, in the process of appeding information from DBF files to tables, the
primary key (T_S_NO) in some instances is left blank, meaning it wont get
added to the database.

What i did was create two tables, one with the primary key (Master Database)
for T_S_NO and one without a primary key (T_S_NO Null) that only contains the
information that has null values. What i was trying to do is create a union
query that goes to both these fields and grabs the information relating to a
specfic sale date, so my SQL statement became this.

Select * From [Master Database] WHERE [Master Database].SALE_DATE<=[Select
Date]
UNION ALL Select * From [T_S_NO Null] Where [T_S_NO Null].SALE_DATE<=[Select
Date];


However, it asks for the sale date input twice. Is there a way to eliminate
this from happening, or does anyone have a better idea as to how to do this?

Thanks!

 

[Royce Schnepp]

SALE_DATE is the field or column in both tables I am trying to query and
combine with the union. So in my SQL statement I have it prompting for the
date the person wants to query, however it asks twice since it is the union
and I have to have search the same date for both. This is I guess what I am
trying to eliminate. If there is someway to have the date entered once and
carried over to the second half of the query, or if I am going about this
setup all wrong and there is a better solution.

I do not see why it would ask for SALE_DATE twice.

Ok, where does [Select Date] come from? Is it a prompt? If that is what
is being asked for twice then you can use a form to input the date in a text
box and then reference the form text box in the query like this --

CVDate([Forms]![YourFormName]![selectDateTextBox])

--
KARL DEWEY
Build a little - Test a little

T_S_NO LST ADDRESS CITY ZIP TG HOEX TRUSTOR OWNER TRUSTEE T_PHONE BENEFRY B_PHONE SALE_DATE SALE_TIME SITE TAX_VALUE TX_YR PRCHS_DATE AMOUNT USE YRBLT SQFT STORY ROOMS LOT LEGAL ASSPAR NOD LOAN NTS TDID REMARKS COUNTY L_DATE
CA-08-146059-PJ 1620 S Pomona Ave C Fullerton 92832 768-H2 Y Frank T.
Carrasco Same Www.priorityposting.com 714 573-1965 Quality Loan 619
645-7711 7/16/2008 10:00 401 E. Chapman Ave.,
Placentia 160949 3 2/6/2003 234,692 CONDO 63 881 4-1-1.0 Tr 8027 Lot 3 Unit
12D1 933-01-103 08-134648 1002630040 08-308897 05-568328 1 7/10/2008
CA-08-145053-SH 801 Stardust Dr Placentia 92870 739-D6 - Jesus
Guerra Same Www.fidelityasap.com 714 259-7850 Quality Loan 619
645-7711 7/16/2008 02:00 Courthouse, 700 Civic Center Dr., Santa
Ana 161068 92 4/15/1991 491,625 R 59 1,188 6-3-2.0 N Tr 3081 Lot
31 339-312-15 08-128734 1000310503 08-308898 06-126820 1 7/10/2008

So that didnt turn out as well as i had hoped...all the bolded stuff are the
columns.

Run a select query without criteria, highlight a bunch of rows, copy, and
paste in a post.
--
KARL DEWEY
Build a little - Test a little


:

How would i post data?

:

it asks for the sale date input twice.
You are showing SALE_DATE as a field in both table therefore it should not
prompt for that information. If [Select Date] is not a field then it would
prompt for that.

Maybe post some sample data from both tables.

--
KARL DEWEY
Build a little - Test a little


:

Here is my dilema if you will. I am working on a database for foreclosure
sales, in the process of appeding information from DBF files to tables, the
primary key (T_S_NO) in some instances is left blank, meaning it wont get
added to the database.

What i did was create two tables, one with the primary key (Master Database)
for T_S_NO and one without a primary key (T_S_NO Null) that only contains the
information that has null values. What i was trying to do is create a union
query that goes to both these fields and grabs the information relating to a
specfic sale date, so my SQL statement became this.

Select * From [Master Database] WHERE [Master Database].SALE_DATE<=[Select
Date]
UNION ALL Select * From [T_S_NO Null] Where [T_S_NO Null].SALE_DATE<=[Select
Date];


However, it asks for the sale date input twice. Is there a way to eliminate
this from happening, or does anyone have a better idea as to how to do this?

Thanks!