Some Sub Query Help....?

[djclntn]

Reverting back to my NASCAR project/db, in 1-query I have the following 4-flds: 1) Owner, 2) CarNum, 3) Series, & 4) CountOfRcWks. Now, the ‘Series’ can be 1 of 3-data – either NNWS, NSCS, or NCWS.

I’ve created a sub query linking the ‘Owners’, however, within this sub query I wish to have another sub query regarding the ‘Series’. After trying many-many different ways ALL day I come here for some advice.

As of now my sub query looks like:
Owners (dropdown)
CarNum


Series
CountOfRcWks

However I would like to have my sub query as:
Owners (dropdown)
Series (dropdown)
CarNum
CountOfRcwks

Any help will be greatly appreciated!

[June7]

Not understanding what the issue is. Why doesn't the query work? We really need to see the SQL statement. Would be nice to see sample of source data and desired output.

I no longer have your db on hand. Want to provide the latest version?

[alansidman]

I think what you are referring to is called cascading combo boxes. Here is a link on how to make them work.

http://www.access-programmers.co.uk/...d.php?t=223980

Look at this and give it a go.

Alan

[djclntn]

June,

After I posted I made a lot of changes to the queries & I don’t remember how I had it. Basically, if you open up the Owners CrTrucks_SQ Query you’ll see a list of Owners & the CountOfCrTrcks they own. If you drop down on an Owner who may have 4 or more CrTrcks you’ll see the ‘CrTrcks’, ‘Series’, & ‘CountOfRcWks’. I would like for the ‘Series’ fld to become another drop down. E.g. if an owner as 4-vehicles 2-in one series (NNWS) & 2-in another Series (NNWS).

Again, I would like the query to be:
Owners (dropdown)
Series (Dropdown)
CrTrcks
CountOfRcWks

[orange]

A lot of us don't have Acc2007 or 2010 and can't use/open your accdb.

Do regular backups.
Don't play with production/your live data.
You can always copy a query, save it with a new name.

What do you want as a result of the query you are wanting to build? In plain English.

However, I suggest you start with a form. But many of us are working blindly - we don't know your table structures.
Create your combo boxes as needed.
cboOwners will have a recordsource based on the Owners table,
cboSeries will have a recordsource based on Series Table

These would be cascading combos as Alan said.
Then, create a SELECT query based on your requirement..

Show the Owner, CarNum, Series, & CountOfRcWks?

[djclntn]

Hi Orange,

I ought to have learned by now that I should always send my dbs in send mdb to cover all bases.

I don't know, I feel as though I explain what results I want at my best, but I suppose others still remain unclear; I'll try to improve.

The example query above is just one of many other queries/sub queries in which I also want to be broken down into 'Series' within sub queries. Therefore

Along with the 2011 Series Results Owners. db which just a part of another db I'm sending along thatcontain NASCAR 3-Series per one year 2011 Series Results. It may give one a clearer picture. Thus far I created 2-Forms pertaing to the Owners with Combo. Boxes. One for 'CrTrcks' & the other for 'Drivers'. I don't know what to do next...

[orange]

I've just downloaded your zip and opened your 2011 Series Results Owners database. I looked at your Form
Owners Drivers Series CountofRcWk to see how the combo box was set up.

I went to Form Design, right clicked on the Owners combo to get Properties, looked at the Data
I have attached the result as a jpg. The record source for the combo doesn't exist???????

Can you spell out in step by step format what exactly you are trying to do?

I'll work with you as I get time, and will base things on the database I have now.
Just be explicit in what you want to do, and we'll work through it.

Nice start - I just sent the post -- have it back now to add the jpg.

[djclntn]

Hi Orange,

Sorry for the delayed; last night I typed out a long explanation, but I suppose I didn’t hit the submit button. First, I’m going to send to you the entire database which contains an accumulation of ALL 3-series in NASCAR for the year 2011. It may be overwhelming to you, but for now just focus on the Owners’ queries. If you’ll open the query, ”Owners CrTrcks CoutOfRcWk" Query it’ll have thfese following flds, ‘Owners’, ‘CrTrcks’ (aka Vehicles), “Series’, & ‘CountOfRcWks’. Now open up the query, “Owners CrTrcks Series_SQ” where you’ll see these flds, ‘Owners’, CountOCrTrcks’ & SumOfRcWks.’ Click on the first owner, “Andrea Nemechek” who has 4-'Series‘ when you drop down Andrea Nemechek you’ll see that she has 2-Vehicles in the NNWS Series & 2-Vehicles in the NSCS Series. What I have been trying to figure out for the past few days is thow can I make the ‘Series’ fld become another dropdown (or cascade) in this query. Whereby, first I have the ’Owners' which I would like to have drop down into the’ Series’ which then dropsdown & displays the ‘Vehicles’ & the ‘CountOfRcWks'. I’ll try to illustrate in branchs (I don’t know what word to use) what I have now & what I want to have my “Owners CrTrcks Series_SQ” to be.

Now it looks as:
- Owners (dropdown)
-- Vehicles
-- Series
-- CountOfRcWk

What I want my “Owners CrTrcks Series_SQ” to be is:
-Owners (dropdown)
-- Series (dropdown)
--- Vehicles
--- CountOfRcwk

I don’t know if what I want in mind is plausible with access. Keep in mind, the diagram I have above for the “Owners CrTrcks Series_SQ”” query is just one of many-many other sub queries within this database where I wish to have the “Series’ fld be a secondary sub query within a sub query – if can be done. Open up the query, “Owners Drivers Series_SQ” it very much similar to the “Owners CrTrcks Series_SQ” query, but instead of the “Vehicles’ it has ‘Drivers’. Let me know if these makes any sense…thanks

[June7]

Those are not 'dropdowns' as would be seen in combobox RowSource which offers values for selection and saving to field of table. Viewing tables/queries directly instead of through form means you are seeing related records. There is no 'cascading' happening here. Merely showing parent/child records of related tables.

If you set up relationships in the Relationship builder, could get the multi-level display of associated records when viewing tables/queries. What you have done, instead of the Relationships builder, is use a query with joined tables to define a relationship. Not sure how another query can be used to carry that out to another level. I don't allow users to work directly with tables/queries for viewing or entering data so never tried anything like this.

[orange]

Thanks june7. I was quite surprised when I looked at the database. As you say, everything (at least the bulk of things) seems to be some combinations of queries. I fully concur that these are not your traditional Forms with combos.
Quite honestly I have never seen a database with such structure. Is there some reason or rationale why the structures are not normalized or why the user (djcintn in this case) is working directly with queries? I think this structure is what has caused some of his questions and frustrations and difficulty in communications. How/What would you recommend to djclntn at this time? It seems he has a lot of historical data from previous years developed with this technique and perhaps it's too late to change basic structures. Anyone have any ideas?