Losing Records Between Union Query and Select Query

R

ryguy7272

Sorry if this is a simple question and just silly, but I am really missing
something here. I have two union queries. The first union query is in the
format that I want, with all the headings set up just fine. The second union
query has all the data I need, but contains a few extra columns. In am using
a select query to eliminate a few unnecessary columns in the second union
query, and get the data in a format that will match the results of the first
union query (I ultimately want to combine the first union query with the
second union query). The problem seems to be with the select query
eliminating several records from the second union query. I can’t figure out
why the select query will truncate many records (102 total). Does anyone
know how to overcome this? Somehow I need to force all records to flow
through from the second union query to the select query.

I’ve used ‘SELECT *’ and I’ve used ‘Union All’ in the past and these have
worked fine in other instances. These don’t seem to work in this instance.

Here is my SQL:
SELECT qryUnionAdvJoilet.RVP, qryUnionAdvJoilet.Director,
qryUnionAdvJoilet.[Sales Rep], qryUnionAdvJoilet.[Related Company],
qryUnionAdvJoilet.[Class 1], qryUnionAdvJoilet.[Class 2],
qryUnionAdvJoilet.Quarter, [Quarter] & "Pipe" AS Source
FROM qryUnionAdvJoilet
GROUP BY qryUnionAdvJoilet.RVP, qryUnionAdvJoilet.Director,
qryUnionAdvJoilet.[Sales Rep], qryUnionAdvJoilet.[Related Company],
qryUnionAdvJoilet.[Class 1], qryUnionAdvJoilet.[Class 2],
qryUnionAdvJoilet.Quarter;


TIA,
Ryan---
 
K

KARL DEWEY

I would gues your totals query is grouping records. Try changing to a
straight select query.
 
J

Jerry Whittle

Karl has probably nailed it. If not do any of the underlying queries use
INNER JOINs? If so try changing them to LEFT or RIGHT JOINS.
 
R

ryguy7272

I finally had a chance to revisit this issue; I just recreated the query and
it worked! I swear I did it the same the second time as I did it the first
time... No apparent reason for why it didn't work the first time, but worked
the second time. Kind of bizarre. Anyway, that that pretty much describes
my whole day.

Thanks for the help guys!!

Ryan---

--
RyGuy


Jerry Whittle said:
Karl has probably nailed it. If not do any of the underlying queries use
INNER JOINs? If so try changing them to LEFT or RIGHT JOINS.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

ryguy7272 said:
Sorry if this is a simple question and just silly, but I am really missing
something here. I have two union queries. The first union query is in the
format that I want, with all the headings set up just fine. The second union
query has all the data I need, but contains a few extra columns. In am using
a select query to eliminate a few unnecessary columns in the second union
query, and get the data in a format that will match the results of the first
union query (I ultimately want to combine the first union query with the
second union query). The problem seems to be with the select query
eliminating several records from the second union query. I can’t figure out
why the select query will truncate many records (102 total). Does anyone
know how to overcome this? Somehow I need to force all records to flow
through from the second union query to the select query.

I’ve used ‘SELECT *’ and I’ve used ‘Union All’ in the past and these have
worked fine in other instances. These don’t seem to work in this instance.

Here is my SQL:
SELECT qryUnionAdvJoilet.RVP, qryUnionAdvJoilet.Director,
qryUnionAdvJoilet.[Sales Rep], qryUnionAdvJoilet.[Related Company],
qryUnionAdvJoilet.[Class 1], qryUnionAdvJoilet.[Class 2],
qryUnionAdvJoilet.Quarter, [Quarter] & "Pipe" AS Source
FROM qryUnionAdvJoilet
GROUP BY qryUnionAdvJoilet.RVP, qryUnionAdvJoilet.Director,
qryUnionAdvJoilet.[Sales Rep], qryUnionAdvJoilet.[Related Company],
qryUnionAdvJoilet.[Class 1], qryUnionAdvJoilet.[Class 2],
qryUnionAdvJoilet.Quarter;


TIA,
Ryan---
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top