Help!!!Query too complicate

[ebasta]

Hiiii
I'm Mara . I write you 'cause maybe you can help me. I'm making a data base with 18 tables all connected with the main one!
Now, I need to search a specific record, introducing alla the needed fields passing through 9 queries. I've already tested the first 5 queries, but when I connect the sixth it tells me that the query is too complicate (there's on.ly one field as the example below), this is maybe because to get the numbers I need I've put as first field in each query

Query N:

Number From Query N-1

So I have to take all my forms (from 1 to 5) open.
I think the problem is this. I need so a table where put all the numbers resulting from the queries(they're 9 queries), but I don't know how, so tath I'll be able to shut down all the previous queries (and relative forms)!

I hope I've been clear and to receive Help, I really need it!

Mara and thank you

 

[ecniv]

Uhhh... uhhhh.. No can't see it sorry.

Can you post up the layout of the main table and say two of the other tables and what exactly you are trying to accomplish?


Vince

 

[ebasta]

Yes, of course, I know I can't easly explain what I would like:

TableSiteSheet
number (as primary key)
other fields

TableLocationSheet
number (connected with TableSiteSheet)
PK (for each table)
Language (text)
other fields

And other 17 tables like these

Then I have of course forms connected with tables to insert datas.Once put in I have other Forms to search specific datas in which I put a listBox connected to queries, like this.

QueryFindRecord1 (In the fisrt form I show all the redords in my Db)
FIELD: number
TABLE: TableSiteSheet

QueryFindRecord2
FIELD: number
TABLE: QueryFindRecord1

QueryFindRecord2
FIELD: number
TABLE: QueryFindRecord2

etc...

In each query ther's a lot of field, this is the reason I divided in severals Forms the search.
As you can see everytimes i open a new Query it needs the previous one.
i don't know how exactly Access works, but I think there's some limits.

What I would like to so is to put all the "number"s selected in a table or something, so that I can shut down all the queries and open only the one I need!

i hope this time I've been clear!!!!

 

[ecniv]

TableSiteSheet
number (as primary key)
other fields

TableLocationSheet
number (connected with TableSiteSheet)
PK (for each table)
Language (text)
other fields

And other 17 tables like these

Just let me clarify this.. there are 17 tables like TableLocationSheet (same fields just different language and other info) ?


Can these not be put together into one huge table?
TableLocationSheet
TableLocID - PK
SiteNo - long (connected with TableSiteSheet)
Language (text) << Put these into another table and use an ID to link?
other fields << these are other associated data fields for this record


As to the forms, you are doing a search on one, then for each successive return you are filtering further each time on the returned subset of data from the previous query (deeeeeep breath).

So I gather that you need to select different records depending on criteria.

Possibly use a form to enter filter data on to then use this data to filter the main records and return only one query with lots of where clause sections. You may want to investigate this. Also you may need to look at the table layout again and see if you can simplify it more. (read up on normalization)

Temp Table
1) Ok, copy the location table, call it TableTempLoc or something.
When writing the Sql Query/using the builder, change the type to Insert Into (addition or something along those line) and choose the TableTempLoc as the destination. Match up the fields, then when you run the query all the matching records are put into the TableTempLoc.
Then you can open a form based on that table to see the results.
Note: Each query that is run is filtered slightly differently and there is a possibility of duplicate records.

2) Another Idea is to dump in the records that match into TableTempLoc then delete the ones you aren't interested in - since its a copy of the data this means a smaller table at the end. It does however mean a huge mdb that will require recompacting afterwards.


Vince

 

[ebasta]

unfortunally this Db contains more then 100 field and they have to stay in different tables because they're 1->infinte case.

So, in each Searching Form I have a list box where I show all my results, to save and use in the next form.
What I could do is to take these results and put them in a table (TempTable), so close the courrent searching form (connected with a query) and open the next one taking the results from TempTable.

So, my new question is:

How can I take results from the listBox?Do I need code?
!!!

Thank you very much!

 

[ecniv]

100 fields - scary!!!

Well the list box is filled via an sql statement, so instead of displaying it (listbox) just send it to the temp table. all you need to do is:

1) delete all records in the temp table (sql like:> delete * from tblTempTable)
2) for each listbox, change the select Sql into Insert into sql statement which you execute (run). After each of these thereshould be rows of data in the temp table
3) Use a listbox or other method to disp those records in the temp table


Vince

 

[ebasta]

I'm sorry, but I can't understand!

In the first Form I have to show all the records, so I can easly connect the listBox (Where I show differents fields) to the main Table (TableSiteSheet).

When I UpDate a Field in the searchForm the macro I use is:
Re-excecute the Query (Sorry for the translation )
So the listBox is Updated too.

If I'd put the field "number" into the TempTable, how can I get all the numbers into the listBox???

 

[The_Doc_Man]

unfortunally this Db contains more then 100 field and they have to stay in different tables because they're 1->infinte case.

By itself, the above statement does not reveal the need to keep these fields in 17 different sub-tables. If you have two 1-to-many tables, they might still be merged successfully. (I did say MIGHT.)

The only GOOD reason one has to make 17 different tables linked to a master table is because 17 different (truly different) things are part of your business model, or are part of the real-world situation you are trying to describe with your database. If you have any two tables that have the same fields but apply to different things, consider adding one field to both that tells you which kind of thing it is, then merging the tables.

Your error message is because you are running into a nesting complexity issue. Look in your Help files for Specifications (for Ac2K and later) or Limits (for Ac97 or earlier) to find out the limits on queries.

For my copy of Ac2K, the limits are things like: 32 tables can be part of a query; queries can be nested 50 levels deep; and you can have up to 40 "AND" conjunctions in WHERE/HAVING clauses. The one that often gets folks is the number of enforced relationships = 32 minus the indexes on participating tables where the indexes don't participate in the query itself.