Lookup function Help

[matthej]

I have the following table which captures a machine type and it’s available space in GB (This table is maintained in Excel….ultimately, I’d like to link Access to this table so that it captures any changes):

Machine Jan Feb Mar Apr
X 3 2 5 4
Y 5 7 8 3


I have a form that has three controls:

1) Machine Type
2) Month
3) GBs

I’m looking for a way to populate the third control based on the table above. So, for example, if the user selected Machine X in the first control, “Jan” in the second, then the third control should automatically be populated with 3. I then use these controls to feed variables in a different report.

What’s the best way to do that? Some sort of Lookup function?

I appreciate any help anyone can give. Or if there’s a specific help topic that covers this, you could reference that as well.

Thanks. Matthej

 

[dhookom]

I wouldn't go much further with this without normalizing the structure. If you can't normalize, you should consider a union query:
SELECT Machine, 1 as Mth, Jan as GBs
FROM tblSpreadsheet
UNION ALL
SELECT Machine, 2, Feb
FROM tblSpreadsheet
UNION ALL
SELECT Machine, 3, Mar
FROM tblSpreadsheet
UNION ALL
--etc--

You can then query based on the machine and month.


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[matthej]

Thanks for the insight. By "normalizing", I'm assuming you mean to change the data into a the following sort of table:

Machine Month GB
X Jan 3
X Feb 2
Y Jan 5
etc.... and then do a select query on that? is that what you mean?

thanks, again.

 

[dhookom]

You are correct regarding the normalizing. However, I would suggest that you don't store values like "Jan", "Feb",... since they provide very little functionality without some type of conversion. Store either a full date value or the month number.

How do you anticipate handling different years?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[matthej]

To be honest, I hadn't considered using the month numbers or accounting for the year. I guess I'll just make that column a full date to make it more useful, as you metioned. Woah...lots to learn about this stuff. thanks again for your help.