vba Queries of Queries?

[Geogeorge]

Hi guys, long time lurker first time poster.
OK I've been using Access for quite a while now however have only really started getting into the nitty gritty of using vba and proper database structure.

My database has 2 tiers of tables linked by a one to many relationship.
Sample>Data
I have over 100 different data types and the occurrence of these is dependent of the type of survey being carried out on the sample position.
For example

Sample ID : Datatype : Value
1 : Cu : 22
1 : Au : 34
1 : Pb : 783
1 : Zn : 27
2 : Au : 78
2 : Zn : 67


I would like to query this table to display certain data type by Sample ID:
Ie.

SampID : Au : Cu: Zn
1 : 34 : 22 : 27
2 : 78 : : 67

I know how to do this using queries of queries with the Design View interface but would like to do so using VBA so that it all occurs 'behind the scenes' without crowding up my database with premade query icons.

Would love some help
George

 

[raskew]

Hi -

Not sure what you're looking for.

I have over 100 different data types

Actually, there are only 13 unique data types:

Boolean
Byte
Integer
Long
Currency
Single
Double
Date
Binary
Text
LongBinary
Memo
GUID

A little more explanation would be helpful.

Bob

 

[Geogeorge]

Sorry should have been more clear on my terminology.
By Data types I mean classifications for data values.

ie. Colour, Mass, Size

Im thinking I should be using left joins of preceding queries to get the result I need though I am unsure how to pass the results of preceding queries to the end query in SQL.

 

[datAdrenaline]

>> I know how to do this using queries of queries with the Design View interface but would like to do so using VBA so that it all occurs 'behind the scenes' without crowding up my database with premade query icons. <<

I am assuming you mean that you are creating a Crosstab query object? If not, that is what you should be creating.

So if you want to retreive a set of records via VBA you have to know what you want to do with those records before a good course of action can be laid out. Or, if you are trying to just modify the SQL property of an existing query object (ie: one "dynamic" query object, but use code to modify the SQL statement stored with the query object), you can use the core line of code that looks something like this:

CurrentDb.QueryDefs("NameOfQuery").SQL = "YourSQLStatement"

Where "NameOfQuery" is the name of the Query Object, and "YourSQLStatement" is a string that follows JET/ACE (aka: Access) SQL syntax.

 

[Geogeorge]

Well they say a picture says a thousand words so its probably easier for the structure to speak for itself rather than me bumble through incorrect terminology.

The image shows the following details
(1) the source data table Note that the SampleOID is the primary key of another table of higher hierarchy.
(2) Shows one of the four preceding queries that select the data value type I would like to display. In this case it is for the "au" values. Note that the criteria value is actually an integer coded value. Treat these which ever way you will.
3) Shows the resulting left join query I have been using with success.

So could anyone please tell me how to do the above in sql/vba form? Or alternatively is my procedure completely wrong?
Thanks in advance.
George

 

[datAdrenaline]

>> Or alternatively is my procedure completely wrong? <<

You really should investigate a "Crosstab" query !!!!

.....

 

[Geogeorge]

On the case Dat

 

[Geogeorge]

Ok so Dat Ive created a Crosstab query and it appears to be working well.
Your a guru! Thank you very much for your help.
I do have one lead on question if you have the time?
In respect to column headings, is it possible to show the text descriptions that I use for my field combo boxes rather than the integer code values? Doing this would make my day
Thanks again

 

[Geogeorge]

Don't worry got it!
Linked the relevant lookup table in the design view box with a relationship and changed the relevant column heading field accordingly.
Thanks again for your constructive help.

 

[datAdrenaline]

You are most welcome! .. Good luck with your project! ...