Query based on another query - incremental?

[Skyshadow5]

I have a table with just one field, there are 56 records in this table. I am using this table as the criteria in a query so everytime I run the query I am prompted for a value (contained in the table). If I make my query a Make Table query, is there any way I can run it so that instead of prompting me for input, it will take the first value in the table, make a table based on that value and call the table "that value + P11" and so on until all 56 records have been called and I have 56 new tables called (e.g.) CS270 P11, CS275 P11, CS280 P11 etc (where CS270, CS275 etc are part of the 56 records)?

 

[mymou]

Hi Skyshadow5

The answer is 'yes, you can' - but I really don't think is sounds like a wise design. Can you give further information about what you are trying to do - so I can see if this is really necessary.

Stew

 

[JeremyNYC]

To add another voice, DON"T DO IT!!

OK, there could possibly be some potential reason that gives some sort of justification for this, but in general, this is a bad idea. Why not just have one table with a bunch of records in it, with one field that holds one of those 56 values for each record? That's one of the fundamental database design principles.

It may be that you know all this and have to produce these tables for a very particular reason, but if the phrase "data normalization" is at all new to you, check out Paul Litwin's article called "Fundamentals of Relational Database Design"--I've got a copy of it in the developers' section of my website.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developers' section of the site for some helpful fundamentals.

 

[Skyshadow5]

Thanks for the replies guys. My problem is that without the table containing the 56 records, my existing query returns multiple instances of the same record but pulling in data from other records. I could explain my overall task but it is a bit long winded. Would looking at the SQL of the queries help at all. I must admit that I do not "understand" SQL but it may help you to grasp what it is I am trying to do. I can, if you like, explain in detail what it is I am attempting to do but my post would be a very long one.

 

[JeremyNYC]

Hmm. The part we were recommending against isn't the table with 56 records, but the 56 individual tables. Why are you making so many tables? =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developers' section of the site for some helpful fundamentals.

 

[Skyshadow5]

I don't want 56 tables but my existing query produces duplicates. If I run it in conjunction with the table that has 56 records, selecting only one of the 56, then I don't get duplicates. So by running it 56 times I get 56 tables and no duplicates. There must be a way though of getting round this but I can't fathom it.

 

[JeremyNYC]

OK, at this point I really don't know what you're trying to do. Can you do a short write-up of what it is you're trying to do?

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developers' section of the site for some helpful fundamentals.

 

[Skyshadow5]

OK here goes, I'll try to keep it brief.
I have a query that, via ODBC, links to an accounting package, specifically a table that stores actual spend and budgets. This query has a criteria specifying an overhead code bewteen x and y (for example). Also a criteria of cost centres between 1 and 9. The last criteria specifies the year. This query works and gives me all instances of actual spend and / or budget of overhead against cost centre. This therefore means that I may 2 lines for some cost centre / overhead combinations. One line for the actual spend that year and another line for the budget for the year. Based on that query I have two more, one that extracts just the actual spend for the year, the other that extracts just the budget info the year. Some cost centre / overhead combinations will therefore be in both of these queries, some will only be in one or the other. My last query combines the actual query and the budget query so that I can have one line per cost centre / overhead combination showing actual for period, budget for period, variance for period (simple formula), actual to date, budget to date, variance to date (simple formula), full budget for year. It is when the last query is run that the duplications occur. For Period 11 I know there are 1200 records in the actual query and 650 in the budget query. Therefore, the maximum there can be for P11 is 1850 records but when run the query returns 18000 ish repeating the cc /ohd many times. There is one other query which is a complete list of the ohd codes that is used with both the actual and budget queries to ensure that all ohds are extracted. Long winded I know, sorry.

 

[mymou]

Hi Skyshadow5

I now have a headache.

Are you saying you want to combine the two subqueries?
1. Actual spend for the year
2. budget info the year

If your not getting the data you expect, I would have thought that your joins are bad - or the data is bad - or not structured the way you think it is.

As everything is coming from one table I imagine there should be a simple query(s) to cover your needs.

Send in some table details - and maybe a bit of SQL. Maybe, a little bit of data to.

Stew