Queries: putting multiple cells of queried data on only one line

[Greg A.]

I have not been able to figure out how to put multiple cells of queried data
on only one line. When I use "Or" statements to query multiple pieces of
information it displays each on a separate line. I am now creating multiple
queries, each time concentrating on only one piece of data at a time and then
utilizing VLOOKUP in Excel to bring it all together on one row.

I look forward to someone's advice.

 

[Jerry Whittle]

Are you using Excel or Access or both? Access does not have cells. Access has
DLookup.

If you are trying to use Access then Excel for the same data, you might be
going about it the hard way. Please show us the SQL statement for your
queries and how you would like to see the results displayed.

 

[John W. Vinson]

I have not been able to figure out how to put multiple cells of queried data
on only one line. When I use "Or" statements to query multiple pieces of
information it displays each on a separate line. I am now creating multiple
queries, each time concentrating on only one piece of data at a time and then
utilizing VLOOKUP in Excel to bring it all together on one row.

I look forward to someone's advice.

There are no "cells" in Access, so it's not quite clear what you're doing -
but to display data from multiple subrecords as one string see
http://www.mvps.org/access/modules/mdl0004.htm

 

[Greg A.]

Hi Jerry,

Here is the SQL code: SELECT Rnl_2007_ALPHA.wksht_cd,
Rnl_2007_RPT.prvdr_num, Rnl_2007_ALPHA.line_num, Rnl_2007_ALPHA.clmn_num,
Rnl_2007_ALPHA.alphnmrc_itm_txt
FROM Rnl_2007_RPT INNER JOIN Rnl_2007_ALPHA ON Rnl_2007_RPT.rpt_rec_num =
Rnl_2007_ALPHA.rpt_rec_num
WHERE (((Rnl_2007_ALPHA.wksht_cd)="s000001") AND
((Rnl_2007_ALPHA.line_num)="00102") AND ((Rnl_2007_ALPHA.clmn_num)="0100" Or
(Rnl_2007_ALPHA.clmn_num)="0200"))
ORDER BY Rnl_2007_RPT.prvdr_num;

I want the following two Fields, ((Rnl_2007_ALPHA.clmn_num)="0100" Or
(Rnl_2007_ALPHA.clmn_num)="0200")) to be in separate columns in the same row.
However, with the above coding they appear in separate rows. I therefore
have been utilizing Excel (VLOOKUP), which I am using for my analysis, to get
the two pieces of data in two separate columns in the same row.

Thanks.

 

[Jerry Whittle]

Save that query. Then create a new query using the Crosstab Query Wizard
based on the original query. It may take a few attemps, but it should do what
you want. There might be a problem where you may have one too many fields,
such as Rnl_2007_ALPHA.alphnmrc_itm_txt, and one will need to be excluded.

 

[Greg A.]

Jerry,

Thanks! That gave me what I wanted!!