If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Union Query
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! |
#2
|
|||
|
|||
Union Query
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 "Royce Schnepp" wrote: 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! |
#3
|
|||
|
|||
Union Query
How would i post data?
"KARL DEWEY" wrote: 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 "Royce Schnepp" wrote: 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! |
#4
|
|||
|
|||
Union Query
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 "Royce Schnepp" wrote: How would i post data? "KARL DEWEY" wrote: 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 "Royce Schnepp" wrote: 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! |
#5
|
|||
|
|||
Union Query
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. "KARL DEWEY" wrote: 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 "Royce Schnepp" wrote: How would i post data? "KARL DEWEY" wrote: 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 "Royce Schnepp" wrote: 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! |
#6
|
|||
|
|||
Union Query
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 "Royce Schnepp" wrote: 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. "KARL DEWEY" wrote: 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 "Royce Schnepp" wrote: How would i post data? "KARL DEWEY" wrote: 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 "Royce Schnepp" wrote: 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! |
#7
|
|||
|
|||
Union Query
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. "KARL DEWEY" wrote: 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 "Royce Schnepp" wrote: 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. "KARL DEWEY" wrote: 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 "Royce Schnepp" wrote: How would i post data? "KARL DEWEY" wrote: 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 "Royce Schnepp" wrote: 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! |
Thread Tools | |
Display Modes | |
|
|