Using a field name in a query to refer to a table

[RKOS92]

Sorry if the title sounds weird.

I have 3 tables called:


Medart
Calow
BP16

These tables each have the same fields. The fields are SP1, SP2, SP3 and SP4.

I have a query and I want to pick out the relevant SP# from one of the tables Medart, Calow, BP16.

There is a field called "Machine" on my input table which allows the user to choose one of Medart, Calow and BP16.

In my query, if I write:
Speed1: ["[Machine]"].SP1

or something similar so that I can easily use the name of the machine input to find the value in the corresponding table

Thanks

[boblarson]

Sorry to put you through it but good practice says I must. Why do you have 3 tables with the same fields? This should not be like this in a properly designed and normalized database. (the reason I ask this, is to hopefully not only teach, but to help you get it to a point which is sustainable and won't require a lot of pain and suffering to get meaningful data out)

[RKOS92]

Sorry to put you through it but good practice says I must. Why do you have 3 tables with the same fields? This should not be like this in a properly designed and normalized database. (the reason I ask this, is to hopefully not only teach, but to help you get it to a point which is sustainable and won't require a lot of pain and suffering to get meaningful data out)

I'm a complete beginner, I've been using Access for about a day and have pretty basic skills in Excel.. I have no idea what a properly designed and normalized database is. What am I supposed to do instead if I want to calculate things more easily?

[boblarson]

Well, the first step is to find out what the data is within your tables and how you have come to have 3 of them.

[RKOS92]

Well, the first step is to find out what the data is within your tables and how you have come to have 3 of them.

The data is feed speeds for different alloys in the 3 different machines. I have a table of information for each machine in the tables "Medart", "Calow" and "BP16" and I need to use the data from the relevant table in calculations in my query depending on the choice of alloy and the choice of machine.. I can't see a way to separate them in any other way :/

[boblarson]

That still doesn't answer the question. How do the three tables get populated? And why do you have one for each machine? If it is manually entered then you should just have ONE table for them all and have an identifier to which machine the record refers. Then you can run a single query to get the information you need about whatever it is you want returned.

[RKOS92]

That still doesn't answer the question. How do the three tables get populated? And why do you have one for each machine? If it is manually entered then you should just have ONE table for them all and have an identifier to which machine the record refers. Then you can run a single query to get the information you need about whatever it is you want returned.

The tables Calow, Medart and BP16 dont contain data that people are entering or changing. The tables contain known feed speeds which the machines operate at, which I will then use to calculate how long different alloys will take to process based on input.

Having one table with 24 fields (and consequently a ton of iif commands) is going to be a lot harder to manage than if I can figure a way of getting what I was trying to do in my first post here.
The fields in this massive table would be:
Calow, Small Diameter Bar, First Pass
Calow, Small Diameter Bar, Second Pass
Calow, Small Diameter Bar, Third Pass
Calow, Small Diameter Bar, Fourth Pass
Calow, Large Diameter Bar, First Pass
Calow, Large Diameter Bar, Second Pass
Calow, Large Diameter Bar, Third Pass
Calow, Large Diameter Bar, Fourth Pass
then the same with the Medart, then the same with the BP16.

Breaking it down into 3 separate tables and using a query saying something like Speed1: ["[Machine]"].SP1 would mean I wouldn't have to distinguish between the 3 different machines as the code would be altered by the choice on the form I'm hoping to make.

I hope I explained it OK..

[boblarson]

Okay, you are a spreadsheet guy and not a relational database guy, as you have said. You need to throw out your spreadsheet thinking and start learning relational database concepts. Okay?

So, you would not have just one table for this. You would have a table setup sort of like this (think ROWS of data, NOT COLUMNS).

tblMachines
MachineID - Autonumber (PK)
MachineName - Text

tblMachineTests
TestID - Autonumber (PK)
MachineTest - Text (example would be: Small Diameter Bar, or Large Diameter Bar)

tblMachineTestPasses
MachineTestPassID - Autonumber (PK)
MachineID - Long Integer (FK from tblMachines)
MachineTestID - Long Integer (FK from tblMachineTests)
PassNumber - Integer
SpeedValue - Whatever Datatype for your test you would need


Then you have a form where you can select the machine, the test (large or small diameter bar) and which pass and then the speed. That would give you the ability to query the data properly.

[RKOS92]

Is there really no way to just use the title of a field like ["[Machine]"].SP1 ?

[boblarson]

Your question is showing that you do not understand what kind of headaches you are setting up for yourself (or anyone that comes after you) when trying to get meaningful data out of this database. So, you could build the query in code but again, you are asking for major trouble heading down that path. (I do have some sort of idea of these things as I have been working with Access and databases in general for almost 15 years now).

[RKOS92]

The thing is, I'm not trying to get a whole lot out.. it's just supposed to calculate how long a bar which the user specifies would take to be machined depending on: the machine, the alloy used, the mass of the bar before machining, the diameter before machining, the quality of the finish (more passes=better quality). This is a tool to help predict how long different bars will take according to some simple information about how long different alloys take to go through the machines. It's not intended to be a summary of all the jobs that have been done so that we can analyse which bars we spend the most time machining, it's just a calculator.

I also have no idea how what you suggested would work never mind help.. why does it say "test" everywhere?