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

 

[Uncle Gizmo]

I can see you've done a lot of work with this. Unfortunately you are going in completely the wrong direction with your Design. It's not possible (for me at least) to work out exactly what's happening in your database. However it's safe to say that I'm sure if you attend to the design flaws it will work much better and be a lot simpler to manage and maintain. Now the design flaws, to give you any idea about those, it is necessary to understand the inputs:- The information you put into the database, and the outputs:- the information you want to get out of the database in your case these are the reports. It would be good to see images of a completed report(s)... Once that is understood then decisions about how the database should be put together can be made. But as I have already indicated if you continue with the structured you've got you are just digging yourself in a deeper hole. For more information about this apparently harsh comment Read this blog here:- "Excel in Access" there are 3 or 4 pages one of them has video instructions see the list on the right hand side of that page....

 

[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

 

[Uncle Gizmo]

From what I can see of your database my guess is that you are monitoring the machining of the train wheels. As the wheels become worn they probably need skimming in a lathe to put them right. My guess is you can only do this so many times before the wheel becomes too thin to be of any more use. I presume you would then scrap them or sell them to a country where there are not so worried about safety!

With regard to having the data in spreadsheets I think you should consider importing the data into a correctly designed table, it would make things so much simpler I reckon.

With regard to the numerous queries an approach I would consider is to create each query with VBA code. This could probably be controlled with the data you already have in other words each query would create itself so to speak. Then as you add more and more information new queries just create themselves. I think that's referred to as a data-driven approach. The data driven approach was used in the original MS Access switchboard. It received a lot of criticism because of its basic design. Basically it look like crap and was difficult to use! However the underlying "DATA DRIVEN CODING" was very good, very informative to any developer.

I'm not sure I would use reports either. I think you might be better off using a form which would probably much be much easier to manipulate with VBA... Having said that there are very many people here with a wide diversity of experience who might be able to provide you with exactly the answer you need.

 

[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