multivalue field in query

[BillCo]

taking the following data:

a w
a n
b r
b y
b p
c a

getting the following result from a query:

a w, n
b r, y, p
c a

without using a function call. (Query returns several thousand rows -
if each one had to execute its own function opening a recordset it
would take unreasonably long to execute... especially since the result
is pulled over MS query into a spreadsheet (via backend on network)...
euch)

possible?

[Anthony England]

"BillCo" <co**********@g mail.com> wrote in message
news:11******** *************@g 10g2000cwb.goog legroups.com...

taking the following data:

a w
a n
b r
b y
b p
c a

getting the following result from a query:

a w, n
b r, y, p
c a

without using a function call. (Query returns several thousand rows -
if each one had to execute its own function opening a recordset it
would take unreasonably long to execute... especially since the result
is pulled over MS query into a spreadsheet (via backend on network)...
euch)

possible?

Converting from one form to the other is time-consuming. Ideally there
would be a way to meet the user requirements without necesarily deciding the
data must be shown in a two-column query. For example, you may be able to
use a report with a multi-column subreport to neatly show a list of all the
related items.

If it really has to be a query, and this is so important and speed is
critical, then you could consider re-designing the database so that it holds
redundant data (shock, horror, break the rules). Provided data is updated
via the front end you provide, you could ensure that whenever the related
table is updated, the main table updates its list of items. Sure MS Access
does not provide triggers which would give you the guarantee that the two
tables remain synchronized, but you could have a pretty good go and include
a checkup routine which would help to build this confidence.

If neither of these options appeal to you, then my final offering is to run
one function which creates one recordset to go through all records and spit
out a summary row for each header, so you create this table locally. I
think I would try this one first and see how long it actually did take
before deciding it will probably be too long. My guess is that even over
the network it would take under 10 seconds.

[paii, Ron]

"BillCo" <co**********@g mail.com> wrote in message
news:11******** *************@g 10g2000cwb.goog legroups.com...

taking the following data:

a w
a n
b r
b y
b p
c a

getting the following result from a query:

a w, n
b r, y, p
c a

without using a function call. (Query returns several thousand rows -
if each one had to execute its own function opening a recordset it
would take unreasonably long to execute... especially since the result
is pulled over MS query into a spreadsheet (via backend on network)...
euch)

possible?

A crosstab query could produce output with each value in it's own column.

a w n
b r y p
c a

[BillCo]

I fear you are correct...
-changing output format is a no-go (used for financial reporting. also
the example i gave is a simplification of much more complicated data
arrangement. i'm not screwing with the entire data structure which at
the moment works)
- changing source data structure is also no-go as the info is pulled in
from an external source as a flat file.
- The only option really realistic is a temp table. still not a great
option though, as the result is pulled via external Ms Query query, not
launched from the db application... the temp table would have to be
generated as part of the start of day routine and stored on the back
end. possible, but messy and a pain in the arse for me.

[BillCo]

one of us is mistaken about how to use a crosstab query!
if you post the sql for how this would actually work, i'll eat my
hat!!! i'm actually not wearing a hat - but i will gladly use your
query structure and thank you perfusely

[Anthony England]

"BillCo" <co**********@g mail.com> wrote in message
news:11******** **************@ i40g2000cwc.goo glegroups.com.. .

I fear you are correct...
-changing output format is a no-go (used for financial reporting. also
the example i gave is a simplification of much more complicated data
arrangement. i'm not screwing with the entire data structure which at
the moment works)
- changing source data structure is also no-go as the info is pulled in
from an external source as a flat file.
- The only option really realistic is a temp table. still not a great
option though, as the result is pulled via external Ms Query query, not
launched from the db application... the temp table would have to be
generated as part of the start of day routine and stored on the back
end. possible, but messy and a pain in the arse for me.

Whatever you can do with MS Query, surely you can do with your own vba
coding? I would certainly see what the quickest function I could write to
create that temp table.
I understand (and had expected) that your actual data structure is more
complex, but still, the temp table only needs to have the two columns. I
could imagine providing a way for users to refresh at will, or auto-refresh
when some report is run.

[paii, Ron]

"BillCo" <co**********@g mail.com> wrote in message
news:11******** *************@g 10g2000cwb.goog legroups.com...

one of us is mistaken about how to use a crosstab query!
if you post the sql for how this would actually work, i'll eat my
hat!!! i'm actually not wearing a hat - but i will gladly use your
query structure and thank you perfusely

TRANSFORM First(TEST.T2) AS [The Value]
SELECT TEST.T1
FROM TEST
GROUP BY TEST.T1
PIVOT TEST.T0;

T1 is your 1st column
T2 is your 2nd column
T0 is a numeric to count output column

1 a w
2 a n
1 b r
2 b y
3 b p
1 c a

You need 3 columns for a cross tab, T0 could be auto number, but you would
get more output columns

[pietlinden]

there's code at AccessWeb that does this fConcatChild.
www.mvps.org/access and then look under the modules section (I think).