UNION QUERY NOT WORKING CORRECTLY

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I NEED HELP WITH MY WHERE STATEMENTS MY UNION QUERY LOOKS LIKE THIS BUT I
NEED A WHERE STATEMENT THAT WILL MAKE MY REPORT ONLY SHOW THE RECORDS THAT
HAVE A NUMBER ENTERED IN THE PRFPART1 FIELD

PLEASE HELP ME


SELECT container, DateSegregated, PRFPART1 , QUANTITYNONCONFORMING1 ,
REASONFORNONCONFORMANCE, PRF, AUTHORIZEDBY
FROM ScrapContainer1

UNION SELECT container, DateSegregated, PRFPART2 , QUANTITYNONCONFORMING2,
REASONFORNONCONFORMANCE, PRF, AUTHORIZEDBY
FROM SCRAPCONTAINER1
UNION SELECT container, DateSegregated, PRFPART3 , QUANTITYNONCONFORMING3,
REASONFORNONCONFORMANCE, PRF, AUTHORIZEDBY
FROM SCRAPCONTAINER1
UNION SELECT container, DateSegregated, PRFPART4 , QUANTITYNONCONFORMING4,
REASONFORNONCONFORMANCE, PRF, AUTHORIZEDBY
FROM SCRAPCONTAINER1
UNION SELECT container, DateSegregated, PRFPART5 , QUANTITYNONCONFORMING5,
REASONFORNONCONFORMANCE, PRF, AUTHORIZEDBY
FROM SCRAPCONTAINER1
UNION SELECT container, DateSegregated, PRFPART6 , QUANTITYNONCONFORMING6,
REASONFORNONCONFORMANCE, PRF, AUTHORIZEDBY
FROM SCRAPCONTAINER1
UNION SELECT container, DateSegregated, PRFPART7 , QUANTITYNONCONFORMING7,
REASONFORNONCONFORMANCE, PRF, AUTHORIZEDBY
FROM SCRAPCONTAINER1
UNION SELECT container, DateSegregated, PRFPART8 , QUANTITYNONCONFORMING8,
REASONFORNONCONFORMANCE, PRF, AUTHORIZEDBY
FROM SCRAPCONTAINER1
UNION SELECT container, DateSegregated, PRFPART9 , QUANTITYNONCONFORMING9,
REASONFORNONCONFORMANCE, PRF, AUTHORIZEDBY
FROM SCRAPCONTAINER1
UNION SELECT container, DateSegregated, PRFPART10 ,
QUANTITYNONCONFORMING10, REASONFORNONCONFORMANCE, PRF, AUTHORIZEDBY
FROM SCRAPCONTAINER1
UNION SELECT container, DateSegregated, PRFPART11 ,
QUANTITYNONCONFORMING11, REASONFORNONCONFORMANCE, PRF, AUTHORIZEDBY
FROM SCRAPCONTAINER1
UNION SELECT container, DateSegregated, PRFPART12 ,
QUANTITYNONCONFORMING12, REASONFORNONCONFORMANCE, PRF, AUTHORIZEDBY
FROM SCRAPCONTAINER1
UNION SELECT container, DateSegregated, PRFPART13 ,
QUANTITYNONCONFORMING13, REASONFORNONCONFORMANCE, PRF, AUTHORIZEDBY
FROM SCRAPCONTAINER1
UNION SELECT container, DateSegregated, PRFPART14 ,
QUANTITYNONCONFORMING14, REASONFORNONCONFORMANCE, PRF, AUTHORIZEDBY
FROM SCRAPCONTAINER1
UNION SELECT container, DateSegregated, PRFPART15 ,
QUANTITYNONCONFORMING15, REASONFORNONCONFORMANCE, PRF, AUTHORIZEDBY
FROM SCRAPCONTAINER1
UNION SELECT container, DateSegregated, PRFPART16 ,
QUANTITYNONCONFORMING16, REASONFORNONCONFORMANCE, PRF, AUTHORIZEDBY
FROM SCRAPCONTAINER1
UNION SELECT container, DateSegregated, PRFPART17 ,
QUANTITYNONCONFORMING17, REASONFORNONCONFORMANCE, PRF, AUTHORIZEDBY
FROM SCRAPCONTAINER1
UNION SELECT container, DateSegregated, PRFPART18 ,
QUANTITYNONCONFORMING18, REASONFORNONCONFORMANCE, PRF, AUTHORIZEDBY
FROM SCRAPCONTAINER1
UNION SELECT container, DateSegregated, PRFPART19 ,
QUANTITYNONCONFORMING19, REASONFORNONCONFORMANCE, PRF, AUTHORIZEDBY
FROM SCRAPCONTAINER1
UNION SELECT container, DateSegregated, PRFPART20 ,
QUANTITYNONCONFORMING20, REASONFORNONCONFORMANCE, PRF, AUTHORIZEDBY
FROM SCRAPCONTAINER1;
 
Set the where clause at the end

Select * from
(SELECT container, DateSegregated, PRFPART1 , QUANTITYNONCONFORMING1 ,
REASONFORNONCONFORMANCE, PRF, AUTHORIZEDBY
FROM ScrapContainer1

UNION SELECT container, DateSegregated, PRFPART2 , QUANTITYNONCONFORMING2,
REASONFORNONCONFORMANCE, PRF, AUTHORIZEDBY
FROM SCRAPCONTAINER1
UNION SELECT container, DateSegregated, PRFPART3 , QUANTITYNONCONFORMING3,
REASONFORNONCONFORMANCE, PRF, AUTHORIZEDBY
FROM SCRAPCONTAINER1
UNION SELECT container, DateSegregated, PRFPART4 , QUANTITYNONCONFORMING4,
REASONFORNONCONFORMANCE, PRF, AUTHORIZEDBY
FROM SCRAPCONTAINER1
UNION SELECT container, DateSegregated, PRFPART5 , QUANTITYNONCONFORMING5,
REASONFORNONCONFORMANCE, PRF, AUTHORIZEDBY
FROM SCRAPCONTAINER1
UNION SELECT container, DateSegregated, PRFPART6 , QUANTITYNONCONFORMING6,
REASONFORNONCONFORMANCE, PRF, AUTHORIZEDBY
FROM SCRAPCONTAINER1
UNION SELECT container, DateSegregated, PRFPART7 , QUANTITYNONCONFORMING7,
REASONFORNONCONFORMANCE, PRF, AUTHORIZEDBY
FROM SCRAPCONTAINER1
UNION SELECT container, DateSegregated, PRFPART8 , QUANTITYNONCONFORMING8,
REASONFORNONCONFORMANCE, PRF, AUTHORIZEDBY
FROM SCRAPCONTAINER1
UNION SELECT container, DateSegregated, PRFPART9 , QUANTITYNONCONFORMING9,
REASONFORNONCONFORMANCE, PRF, AUTHORIZEDBY
FROM SCRAPCONTAINER1
UNION SELECT container, DateSegregated, PRFPART10 ,
QUANTITYNONCONFORMING10, REASONFORNONCONFORMANCE, PRF, AUTHORIZEDBY
FROM SCRAPCONTAINER1
UNION SELECT container, DateSegregated, PRFPART11 ,
QUANTITYNONCONFORMING11, REASONFORNONCONFORMANCE, PRF, AUTHORIZEDBY
FROM SCRAPCONTAINER1
UNION SELECT container, DateSegregated, PRFPART12 ,
QUANTITYNONCONFORMING12, REASONFORNONCONFORMANCE, PRF, AUTHORIZEDBY
FROM SCRAPCONTAINER1
UNION SELECT container, DateSegregated, PRFPART13 ,
QUANTITYNONCONFORMING13, REASONFORNONCONFORMANCE, PRF, AUTHORIZEDBY
FROM SCRAPCONTAINER1
UNION SELECT container, DateSegregated, PRFPART14 ,
QUANTITYNONCONFORMING14, REASONFORNONCONFORMANCE, PRF, AUTHORIZEDBY
FROM SCRAPCONTAINER1
UNION SELECT container, DateSegregated, PRFPART15 ,
QUANTITYNONCONFORMING15, REASONFORNONCONFORMANCE, PRF, AUTHORIZEDBY
FROM SCRAPCONTAINER1
UNION SELECT container, DateSegregated, PRFPART16 ,
QUANTITYNONCONFORMING16, REASONFORNONCONFORMANCE, PRF, AUTHORIZEDBY
FROM SCRAPCONTAINER1
UNION SELECT container, DateSegregated, PRFPART17 ,
QUANTITYNONCONFORMING17, REASONFORNONCONFORMANCE, PRF, AUTHORIZEDBY
FROM SCRAPCONTAINER1
UNION SELECT container, DateSegregated, PRFPART18 ,
QUANTITYNONCONFORMING18, REASONFORNONCONFORMANCE, PRF, AUTHORIZEDBY
FROM SCRAPCONTAINER1
UNION SELECT container, DateSegregated, PRFPART19 ,
QUANTITYNONCONFORMING19, REASONFORNONCONFORMANCE, PRF, AUTHORIZEDBY
FROM SCRAPCONTAINER1
UNION SELECT container, DateSegregated, PRFPART20 ,
QUANTITYNONCONFORMING20, REASONFORNONCONFORMANCE, PRF, AUTHORIZEDBY
FROM SCRAPCONTAINER1
)
Where prfpart1 = ???
 
When i do this it changes my union query to a select query? am i supposed to
creat another query with the sql string you gave me? also when i do that then
save it it changes to this

SELECT *
FROM [SELECT container, DateSegregated, PRFPART1 , QUANTITYNONCONFORMING1 ,
REASONFORNONCONFORMANCE, PRF, AUTHORIZEDBY FROM ScrapContainer1 UNION SELECT
container, DateSegregated, PRFPART2 , QUANTITYNONCONFORMING2,
REASONFORNONCONFORMANCE, PRF, AUTHORIZ] AS [%$##@_Alias]
WHERE prfpart1=True;


The Microsoft Jet database engine cannot find the input table or query
<SELECT container, DateSegregated, PRFPART1 , QUANTITYNONCONFORMING1 ,
REASONFORNONCONFORMANCE, PRF, AUTHORIZEDBY FROM ScrapContainer1 UNION SELECT
container, DateSegregated, PRFPART2 , QUANTITYNONCONFORMING2,
REASONFORNONCONFORMANCE, PRF, AUTHORIZ>. Make sure it exists and that its
name is spelled correctly. (Error 3078)

it works until i close it and save it then when i reopen it these are the
errors i get
please let me know if you can help. am i suppused to be putting something in
for the *?

thanks

Brien
 
Union queries combine corresponding fields from two or more tables or
queries into one field. It looks like the query draws information from
only one table. In this case, you might try adjusting your query. For
example, SELECT container, DateSegregated, PRFPART1, PRFPART2, PRFPART3
etc through PRFPART20, QUANTITYNONCONFORMING1 etc through
QUANTITYNONCONFORMING20,
REASONFORNONCONFORMANCE, PRF, AUTHORIZEDBY
FROM ScrapContainer1
WHERE PRFPART1 is not null.

Something along those lines.
 
i made the union query to combine fields from 1 table because i have to make
a report and this is the only way i could think to make a report look the way
i want maybe you can help me out

i have records that look like this
[Container][Date][PT1][Qty1][PT2][Qty2][PT3][Qty3][ReasonForNC][PRF][Authorized]

all in one row


and i want it to look like this
[Container][Date][PT1][Qty1][ReasonForNC][PRF][Authorized]
[Container][Date][PT2][Qty2][ReasonForNC][PRF][Authorized]
[Container][Date][PT3][Qty3][ReasonForNC][PRF][Authorized]
 
When you get out of this difficulty, you might consider restructuring
your original table. One suggestion on this note is to have a field
called "PT" and have "PT1"; "PT2"; "PT3"; as options for field data.
The same goes for the "Qty" fields.

For the present, if you have datum in one of the PT fields, and only if
not more than one PT field at a time for each record, you can create a
query and add a field, e.g., [PTall] and use a formula to show the
value in each PT field, for example,
PT:=iif([PT1]<>"",[PT1],iif([PT2]<>"",[PT2],[PT3])). The more PT
fields, the longer the nested conditional statements. Of course you
would do the same for the Qty fields.

Somebody else here probably has a much more brilliant solution. Sorry
I could not help you better.
 
Back
Top