difficulty creating a query for many reports

[109bow]

Hi all,
I'm not sure this post is in the right category, but here goes!
I'm still getting to grips with Access, so please bear with me. I have form "front page 2" from which I can select 2 numbers from 2 combo boxes. The 2 combo boxes refer to the 2 halves of a train, known as north and south units. When a north unit is coupled to a south unit it then becomes a train.
With 2 numbers selected in the 2 combo boxes, I can then press 1 of the 3 buttons, coloured in different shades of purple. Each button print a different combination of reports.
As a starting point I have created many queries, which ultimately give the last data for each wheel fitted to the 2 units selected. Then a final query "record sheets" pulls the 32 queries for each wheel together along with the data entered on "front page 2" into one line, using lots of DLookup's. This query is then the source for all the reports. This works, sort of, as it is unsurprisingly very slow, especially when all this is part of a bigger DB.
In an attempt to speed things up, I have created query "lastturn3" which gives me the last record for each wheel. I am now at a loss as to how I can get the data into the correct boxes in all the reports.
Any suggestions very, very welcome.
Thanks

 

[plog]

Agree with Gizmo. Your tables are not structured properly. You are building on an improper foundation and need to secure it. You need to normalize (https://en.wikipedia.org/wiki/Database_normalization) your tables.

Without knowing about your data, I can't tell you what that proper structure is, but I do see that you consistently store data values in field names. Instead of all those left/right fields the "left", "right" values need to be stored within the fields themselves. Most likely in new tables. Again, read up on normalization and give your structure another shot.

Lastly, name your fields better. Only use alpha-numeric characters (that means no spaces) and do not use reserved words (https://support.office.com/en-us/ar...-symbols-E33EB3A9-8BAA-4335-9F57-DA237C63EABE) like 'Date'. Instead prefix it with what that date is for (TurnDate, InspectionDate, etc.).

 

[109bow]

Uncle Gizmo, thanks for your reply. Both tables "Accumulative km" and "DATAEXPORT_5" in my original database are linked tables to excel files, so there is little I can with them, only for the purpose of posting sample1 DB I have made them normal tables. I have attached a screenshot of one of the reports and the form front page 2.
I will look into the link you have given.
Thanks

 

[109bow]

attached is a word document that has screen shots of the two linked tables, "DATAEXPORT_5" and "Accumulative km"

 

[plog]

You can and should still normalize your data. From where you are now, without altering the spreadsheets there are 2 ways to achieve this:

1. Build a warehouse. This will have properly structured tables and whenever you need to refresh the data in them, you clear out the data then build a process of INSERT and APPEND queries that moves the data from the spreadsheets into them.

2. Build queries simulating the proper structure. Very similar to #1, except instead of the process being a series of INSERT and APPEND queries they are SELECT queries which would model the properly laid out tables.

Since you initially posted about speed of the queries, #1 would be the fastest option, but would not contain up to the second data--just data from your last warehouse construction.

 

[109bow]

plog, thanks for this. As it is not possible to alter the source data, what I need is to create a table and update it with Insert and Append queries then.
I will give this a go, but not used Insert, Append and Update queries, so might take a while.
I assume this update of the created table can be done every time the DB is opened?
Thanks

 

[plog]

I assume this update of the created table can be done every time the DB is opened?

Yes, but there's no telling how long that process might take. Also, if multiple people have access to the database it might cause a current user to get errors as the new user runs the update process.

Of course the first thing is to set up your tables properly.

 

[109bow]

Uncle Gizmo, you are absolutely correct, I am monitoring the condition of train wheels and planning when the wheels need turning. After turning a number of times, the wheels are then too small to turn anymore and are sent away to be re-wheeled, much the same as having a new tyre put on a car wheel.
As Plog has suggested, I will have a go at creating table that is updated using queries.
I will look into the data driven approach, but once again this is something I've not come across before.

 

[109bow]

Plog, at the moment only user at any one time will be using the DB, so that shouldn't be a problem.
Will get onto creating that table