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
|
|||
|
|||
Losing Records Between Union Query and Select Query
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--- -- RyGuy |
#2
|
|||
|
|||
Losing Records Between Union Query and Select Query
I would gues your totals query is grouping records. Try changing to a
straight select query. -- KARL DEWEY Build a little - Test a little "ryguy7272" wrote: 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--- -- RyGuy |
#3
|
|||
|
|||
Losing Records Between Union Query and Select Query
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" wrote: 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--- -- RyGuy |
#4
|
|||
|
|||
Losing Records Between Union Query and Select Query
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" wrote: 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" wrote: 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--- -- RyGuy |
Thread Tools | |
Display Modes | |
|
|