Query on field name and primary key

[randommetalguy]

Hey guys,

I know how to do this is Excel but don't now how to convert to Access yet.

I have a table named Number of Parts that looks like this:

PK | 1000 | 2000
500 | 67 | 75
650 | 72 | 80

How would I write a query to return the value 67 if I have the values 1000 and 500 as input from my user.

Thanks

 

[pbaldy]

Well, the query would be:

SELECT 1000
FROM TableName
WHERE PK = 500

That said, having the user enter the 500 value would be fairly easy to handle. Having them enter the 1000 is more problematic. A query can handle a dynamic criteria easily, but not a field. What do the 1000 and 2000 represent?

 

[ezfriend]

perhaps, select tablename.[1000] from tablename where pk = 500

 

[pbaldy]

Actually you probably would have to bracket the fieldname, but you still face the problem of getting the field name from user input.

 

[randommetalguy]

Well 2 of the 3 inputs are going to be from a drop down list. Looking at my tables more closely, I recognized that the 3 tables use similar field names so drop down menus will probably help me eliminate the problematic nature of entering a field name.

1000 will be a measurement in mm. If the user selects a measurement that my tables do not support, I'll just spit out a message like "The standard you have chosen down not have that measurement."

But how do I get my query to read the value from the drop down list? Should I just have one huge if statement for my 15 different measurements? Something like....

If (cboMeasurement.text == 1000)
{
select tablename.[1000] from tablename where pk = 500
}
else if (cboMeasurement.text == 1200)......

Also, I'm pretty new to using Access, I've only been using SQL with Oracle databases. Can anyone direct me to a primer for Access Application Programming?

 

[pbaldy]

Well, I'm not sure I understand the data completely, but a layout like this would make it a lot easier to deal with:

PK   -   Measurement      -        Value
500             1000               67
500             2000               75
650             1000               72
650             2000               80

Because then your query is:

SELECT Value
FROM TableName
WHERE PK = Forms!... AND Measurement = Forms!...

 

[randommetalguy]

Okay I've thought about making just a measurements table similar to the one you have described. That might be what I have to do. I'll explore this more later on tonight and get back to you guys in the morning.

In the meantime, if anyone has links to articles that talk about using forms and reports with access to make an 'Access Application' it would be much appreciated.