SUPERLONG queries 2

[kszacki]

Hi
I have 2 questions.

1)
How can I set (using VB code) which query columns are visible in the query result?
Like using "SHOW" switch in the queries design view.

2)
Is it possible to make "superlong" query? It means... for ex., I have database containing products and history of all prices each product ever had.
What I want to do is...
Make a query which displays IN THE ONE ROW !!! (from the left) product name and all available historical prices.
Every product can have different number of historical prices.
Let say... 250 columns would be enough for me.



Charles

 

[tlbroadbent]

1) Just omit columns from the Select List if you don't want to Show them.

2) Use a Cross-Tab query to accomplish this. Use the Cross-Tab query wizard to help build the query. You may run into limitations on the number of columns in such a query. Terry Broadbent
Please review faq183-874.

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin

 

[Philly44]

I can't help with the first problem but I have an idea that might help for the second one. I work i a rather large Access database that tracks Customer Information. I have quite a few reports that require the same type of output. The recordsource I set for these is massive UNION quiries. I don't know if your familiar with SQL but you should search the net for some examples. If you need some examples just let me know and I'll post some source for you.

 

[kszacki]

Yes please... examples would be very helpful.

Charles

 

[Philly44]

Sorry if this a little compiated and hard to read, its hard to format more friendly with limited room. What this code does is it counts instances of the specified text making references to 2 tables. Then first part returns the values through the the variables Entity and Fequency, then it reuses the variables again for the next set of values.


SELECT "61a " & "Self Guided" AS Entity, Count(*) & " Total" AS Frequency
FROM tblSchoolInfoRequested INNER JOIN tblSchoolInfoRequestedTest ON tblSchoolInfoRequested.RequestID = tblSchoolInfoRequestedTest.RequestID
WHERE ((SelfGuided = Yes) AND (DateOfCall Between [Forms]![frmDateRange]![txtStartDate] AND [Forms]![frmDateRange]![txtEndDate]) AND (SiteID = SiteName()))
UNION
SELECT "61b - " & LangSelfGuided AS Entity, Count(*) AS Frequency
FROM tblSchoolInfoRequested INNER JOIN tblSchoolInfoRequestedTest ON tblSchoolInfoRequested.RequestID = tblSchoolInfoRequestedTest.RequestID
WHERE ((LangSelfGuided Is Not Null) AND (DateOfCall Between [Forms]![frmDateRange]![txtStartDate] AND [Forms]![frmDateRange]![txtEndDate]) AND (SiteID = SiteName()))
GROUP BY LangSelfGuided
HAVING LangSelfGuided Is Not Null

 

[kszacki]

Thank YOU all

Charles