 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / General 1 / March 2006multivalue field in query
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? > taking the following data: > [quoted text clipped - 18 lines] > > 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. 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. >I fear you are correct... > -changing output format is a no-go (used for financial reporting. also [quoted text clipped - 8 lines] > 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. > taking the following data: > [quoted text clipped - 18 lines] > > possible? A crosstab query could produce output with each value in it's own column. a w n b r y p c a 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 > 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 there's code at AccessWeb that does this fConcatChild. www.mvps.org/access and then look under the modules section (I think). |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.