variable in query?

[ztm]

Hello all,

I’m creating an Access database to track machine maintenance. I have a table that lists the standard costs of each type of PM (periodic maintenance) for each machineID. The fields in the table are MachineID (number), Daily (currency), Weekly (currency), Monthly (currency), etc. I’ve set up a form to enter maintenance history into a table and I want to automatically fill in the cost of a specific PM. I have drop down combo boxes that look up available Machines, type of maintenance, etc. When the user selects a machine, and then a PM, say, a Quarterly, I want to look up the cost of a Quarterly for the selected machine and put that amount in a text box. I’d like to use a parameter based select query for the rowsource that uses a variable for the field name but don’t know how or if it’s possible. Maybe something like this.

SELECT [PMCost].[MachineID],[PMCost].[fieldname variable (Daily, Weekly, etc] WHERE ((([PMCost].[MachineID])=[Forms]![frmName]![ControlName]) AND ([PMCost].[fieldname variable]) = [Forms]![frmName]![ControlName]))

 

[JerryDennison]

Why not normalize the data better? That way you won't have to "finagle" with fieldnames in queries. Add a table for you maintenance costs. Include a foreign key to the machineid, a field for the type (i.e. daily, weekly, monthly, whatever), and a field for the cost. Then for each machine you could have no PM costs to all PM costs associated with it.

BTW, your current structure violates the 1NF (First Normal Form). All attributes must be atomic and there should be no repeating groups.