Query is too complex?

[accuss]

Hello. I have a simple query. It queries 3 fields from tables. And only 1 of the fields has criteria, which is Date().

The only problem is that it queries 35 tables. When I put the 35th in there, I cannot run the query. I've tried to change the field names to something smaller, to give Access less to load. That did not help.

Is there any trick that I could use to get past this?

Thanks in advance!

 

[Minty]

35 Tables at once?
That sounds like a design error.

Can you post up the SQL of the query or a picture of the query structure from the query designer?

 

[accuss]

It queries notes from work done on 35 different machines. One table per machine. There are 2 shifts. One field per shift, for notes.

SELECT Minutesof1V18tbl.Dateof, Minutesof1V18tbl.NotesShift1, Minutesof1V18tbl.NotesShift2, Minutesof200V1FTtbl.Dateof, Minutesof200V1FTtbl.NotesShift1, Minutesof200V1FTtbl.NotesShift2, Minutesof200V4FTtbl.Dateof, Minutesof200V4FTtbl.NotesShift1, Minutesof200V4FTtbl.NotesShift2, Minutesof200V6FT1tbl.Dateof, Minutesof200V6FT1tbl.NotesShift1, Minutesof200V6FT1tbl.NotesShift2, Minutesof200V6FT2tbl.Dateof, Minutesof200V6FT2tbl.NotesShift1, Minutesof200V6FT2tbl.NotesShift2, Minutesof24SFTtbl.Dateof, Minutesof24SFTtbl.NotesShift1, Minutesof24SFTtbl.NotesShift2, Minutesof2A12FTtbl.Dateof, Minutesof2A12FTtbl.NotesShift1, Minutesof2A12FTtbl.NotesShift2, Minutesof2A14FTtbl.Dateof, Minutesof2A14FTtbl.NotesShift1, Minutesof2A14FTtbl.NotesShift2, Minutesof2A3FT1tbl.Dateof, Minutesof2A3FT1tbl.NotesShift1, Minutesof2A3FT1tbl.NotesShift2, Minutesof2A3FT2tbl.Dateof, Minutesof2A3FT2tbl.NotesShift1, Minutesof2A3FT2tbl.NotesShift2, MinutesofLEWIS2CVtbl.Dateof, MinutesofLEWIS2CVtbl.NotesShift1, MinutesofLEWIS2CVtbl.NotesShift2, MinutesofROLLTORSION1tbl.Dateof, MinutesofROLLTORSION1tbl.NotesShift1, MinutesofROLLTORSION1tbl.NotesShift2, MinutesofRollTorsion2tbl.Dateof, MinutesofRollTorsion2tbl.NotesShift1, MinutesofRollTorsion2tbl.NotesShift2, MinutesofRollTorsion3tbl.Dateof, MinutesofRollTorsion3tbl.NotesShift1, MinutesofRollTorsion3tbl.NotesShift2, Minutesof2A4VFtbl.Dateof, Minutesof2A4VFtbl.NotesShift1, Minutesof2A4VFtbl.NotesShift2, MinutesofSIAVStbl.Dateof, MinutesofSIAVStbl.NotesShift1, MinutesofSIAVStbl.NotesShift2, MinutesofTAK1tbl.Dateof, MinutesofTAK1tbl.NotesShift1, MinutesofTAK1tbl.NotesShift2, MinutesofTAK2tbl.Dateof, MinutesofTAK2tbl.NotesShift1, MinutesofTAK2tbl.NotesShift2, MinutesofTAK3tbl.Dateof, MinutesofTAK3tbl.NotesShift1, MinutesofTAK3tbl.NotesShift2, Minutesof5Atbl.Dateof, Minutesof5Atbl.NotesShift1, Minutesof5Atbl.NotesShift2, Minutesof200V12FTtbl.Dateof, Minutesof200V12FTtbl.NotesShift1, Minutesof200V12FTtbl.NotesShift2, Minutesof300Vtbl.Dateof, Minutesof300Vtbl.NotesShift1, Minutesof300Vtbl.NotesShift2, Minutesof400EVtbl.Dateof, Minutesof400EVtbl.NotesShift1, Minutesof400EVtbl.NotesShift2, Minutesof500Vtbl.Dateof, Minutesof500Vtbl.NotesShift1, Minutesof500Vtbl.NotesShift2, Minutesof400vTBL.Dateof, Minutesof400vTBL.NotesShift1, Minutesof400vTBL.NotesShift2, MinutesofTAK4TBL.Dateof, MinutesofTAK4TBL.NotesShift1, MinutesofTAK4TBL.NotesShift2, MinutesofCG1TBL.Dateof, MinutesofCG1TBL.NotesShift1, MinutesofCG1TBL.NotesShift2, MinutesofCG2TBL.Dateof, MinutesofCG2TBL.NotesShift1, MinutesofCG2TBL.NotesShift2, MinutesofCG3TBL.Dateof, MinutesofCG3TBL.NotesShift1, MinutesofCG3TBL.NotesShift2, MinutesofCG4TBL.Dateof, MinutesofCG4TBL.NotesShift1, MinutesofCG4TBL.NotesShift2, MinutesofCG5TBL.Dateof, MinutesofCG5TBL.NotesShift1, MinutesofCG5TBL.NotesShift2, MinutesofCG6TBL.Dateof, MinutesofCG6TBL.NotesShift1, MinutesofCG6TBL.NotesShift2, Minutesof200V16FTtbl.Dateof, Minutesof200V16FTtbl.NotesShift1, Minutesof200V16FTtbl.NotesShift2
FROM Minutesof1V18tbl, Minutesof200V1FTtbl, Minutesof200V4FTtbl, Minutesof200V6FT1tbl, Minutesof200V6FT2tbl, Minutesof24SFTtbl, Minutesof2A12FTtbl, Minutesof2A14FTtbl, Minutesof2A3FT1tbl, Minutesof2A3FT2tbl, MinutesofLEWIS2CVtbl, MinutesofROLLTORSION1tbl, MinutesofRollTorsion2tbl, MinutesofRollTorsion3tbl, Minutesof2A4VFtbl, MinutesofSIAVStbl, MinutesofTAK1tbl, MinutesofTAK2tbl, MinutesofTAK3tbl, Minutesof5Atbl, Minutesof200V12FTtbl, Minutesof300Vtbl, Minutesof400EVtbl, Minutesof500Vtbl, Minutesof400vTBL, MinutesofCG1TBL, MinutesofCG2TBL, MinutesofCG3TBL, MinutesofCG4TBL, MinutesofCG5TBL, MinutesofCG6TBL, MinutesofTAK4TBL, Minutesof200V16FTtbl
WHERE (((Minutesof1V18tbl.Dateof)=Date()) AND ((Minutesof200V1FTtbl.Dateof)=Date()) AND ((Minutesof200V4FTtbl.Dateof)=Date()) AND ((Minutesof200V6FT1tbl.Dateof)=Date()) AND ((Minutesof200V6FT2tbl.Dateof)=Date()) AND ((Minutesof24SFTtbl.Dateof)=Date()) AND ((Minutesof2A12FTtbl.Dateof)=Date()) AND ((Minutesof2A14FTtbl.Dateof)=Date()) AND ((Minutesof2A3FT1tbl.Dateof)=Date()) AND ((Minutesof2A3FT2tbl.Dateof)=Date()) AND ((MinutesofLEWIS2CVtbl.Dateof)=Date()) AND ((MinutesofROLLTORSION1tbl.Dateof)=Date()) AND ((MinutesofRollTorsion2tbl.Dateof)=Date()) AND ((MinutesofRollTorsion3tbl.Dateof)=Date()) AND ((Minutesof2A4VFtbl.Dateof)=Date()) AND ((MinutesofSIAVStbl.Dateof)=Date()) AND ((MinutesofTAK1tbl.Dateof)=Date()) AND ((MinutesofTAK2tbl.Dateof)=Date()) AND ((MinutesofTAK3tbl.Dateof)=Date()) AND ((Minutesof5Atbl.Dateof)=Date()) AND ((Minutesof200V12FTtbl.Dateof)=Date()) AND ((Minutesof300Vtbl.Dateof)=Date()) AND ((Minutesof400EVtbl.Dateof)=Date()) AND ((Minutesof500Vtbl.Dateof)=Date()) AND ((Minutesof400vTBL.Dateof)=Date()) AND ((MinutesofTAK4TBL.Dateof)=Date()) AND ((MinutesofCG1TBL.Dateof)=Date()) AND ((MinutesofCG2TBL.Dateof)=Date()) AND ((MinutesofCG3TBL.Dateof)=Date()) AND ((MinutesofCG4TBL.Dateof)=Date()) AND ((MinutesofCG5TBL.Dateof)=Date()) AND ((MinutesofCG6TBL.Dateof)=Date()) AND ((Minutesof200V16FTtbl.Dateof)=Date()));

 

[arnelgp]

Is there any trick that I could use to get past this?

there is no patch for this.
you need to find a way to use 1 table only for all these machines.
you can add a column for the Machine Name.

that is a serious mistake you have.

create a new table and consolidate all your tables to this one.

 

[accuss]

there is no patch for this.
you need to find a way to use 1 table only for all these machines.
you can add a column for the Machine Name.

that is a serious mistake you have.

create a new table and consolidate all your tables to this one.

Thank you for your advise.

 

[Guus2005]

In the end your query will look like this:

SELECT MachineName, Dateof, NotesShift1, NotesShift2
FROM Minutes
WHERE Dateof = Date()

Access will have no trouble executing that query for you!

Read the normalisation link from minty!

HTH

 

[Minty]

@arnelgp Has hit the Nail on the head. One table with a machine number.
Then your query will be significantly simpler, as will the rest of your database world moving forwards.

 

[Pat Hartman]

In addition to the good advice you've gotten as to how to resolve the issue, I would also like to point out the very real problem with your From clause.

You have no joins defined. That means that you are creating a Cartesian Product. This query is multiplying the number of rows in the first table times the number in the second times the number in the third, .... times the number in the 35th. Eventually, you run out of memory. I don't know how many rows in each table but even if it is only 100, the resultset gets huge in a hurry. 100 * 100 * 100 * 100 * ..... for all thirty five tables. After just the first 4 tables, you are up to 100,000,000 rows!!!!!!!!!!!!!!!!!!! It is far more likely that your intention is a union which would be 100 + 100 + 100 + 100 .... In this case, you are probably running into the max number of tables that can be included in any query rather than running out of memory to hold all the gows generated by the Cartesian Product.

So, although a union query or more likely several unions of unions might solve the problem, the real fix is to use a single table to hold all the data with an additional column to identify type. That will allow you to also probably get rid of dozens of duplicate forms and queries.

Stand back and put your thinking cap on. How can any application of any size possibly be defined with a separate table per item. What would Amazon do with the millions of items it sells? Could they possibly have defined MILLIONS of separate tables? One for each product they sell? Please read the Normalization links posted by Minty.

 

[accuss]

In the end your query will look like this:

SELECT MachineName, Dateof, NotesShift1, NotesShift2
FROM Minutes
WHERE Dateof = Date()

Access will have no trouble executing that query for you!

Read the normalisation link from minty!

HTH

That does seem like a much better approach. However, It looks like I would have to create the fields that you've mentioned. But ones for each machine, per date? I need to have a daily record kept.

 

[plog]

I need to have a daily record kept.

Why not a new table for each date? Or a new field for each date? I'll tell you why---because you have a generic date field for that and you simply store the value of the date inside the field---not in a field name or the table's name.

Same principle for every other piece of data. You don't store actual data in names--you store that in fields with generic names. From what you've stated thus far, Guus nailed all the fields you need:

MachineName, DateOf, NotesShift1, NoteShift2

Every piece of data you have fits into 1 of those fields, no need for another field or another table.

 

[accuss]

Why not a new table for each date? Or a new field for each date? I'll tell you why---because you have a generic date field for that and you simply store the value of the date inside the field---not in a field name or the table's name.

Same principle for every other piece of data. You don't store actual data in names--you store that in fields with generic names. From what you've stated thus far, Guus nailed all the fields you need:

MachineName, DateOf, NotesShift1, NoteShift2

Every piece of data you have fits into 1 of those fields, no need for another field or another table.

OK, thank you. I was just having a hard time wrapping my head around making this adjustment. But I think I can figure this one out. Thanks again.

 

[accuss]

OK, so the one table with 6 fields approach can be used without duplicate creating entries for dates per machine? For example, if I'm on machine 1, and I enter a description of my work into the DB. And then the 2nd shift comes in and there's another guy working on the same machine. When he's done, he enters a description of his work on the machine. If we both go through the same path to do what we do, recording our work, how are duplicates prevented?

 

[plog]

This is going to blow your mind--but the answer is less fields.

A new table/field for every machine is definitely wrong, but you only listed 2 shifts so I let it slide. But technically, you shouldn't have a field for each shift. You should have a generic [Shift] field and you enter the number in there.

So now your table should be this:

MachineName, DateOf, Shift

Then you make all 3 of those fields the composite key:

Add or change a table’s primary key in Access - Microsoft Support

An Access primary key in an Access database table is a field with unique values for each record in the table. You can use the key to connect data between tables.

support.microsoft.com

And you will ensure that you never have duplicates

 

[CJ_London]

how are duplicates prevented?

you get your user to enter the machine, date and shift before they enter a description of the work. Perhaps date and shift can be determined by the time the description was entered. Or perhaps the user enters their name (or selects from a list) and you have another table which says they work a specific shift