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.

 

[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