Query table for list box

[jllbee]

ok, I have a table that has Items in Rows and Style's in Colums:

ITEM	STYLE A	STYLE B	STYLE C	STYLE D	STYLE E	STYLE F	STYLE G	STYLE H
1	$1.00		$2.00			$3.00
2		$2.00
3
4	$1.00				$3.00
5
6			$5.00				$6.00
7	$1.00
8
9					$2.00			$3.00
10	$1.00

I have a form that has two list boxes:



I want to Choose the Item and then have the Style Listbox only show Style's that have a price in them.
This table has 406 Rows and 72 colums

I can do this in excel, but i'm not a Access Programmer and using MS Access 2003.
how do i build this query?

Thanks in advance
JL

[isladogs]

You have a spreadsheet layout which doesn't work well in Access
Firstly you should redesign your table with 4 fields
ItemID - autonumber PK
Item - number
Style - Text (1)
Price - Currency

You would then have several records for each item each with a price - 3 for item 1, 1for item 2, none for item 3 etc
Then use cascading combo boxes where the second combo just has the style records for the item selected in the first combo.
Trying to do this in Access with your design will be a nightmare. If you want that layout, use Excel

[Peter M]

I just re-read your post; you have 72 columns?
You probably need a VBA script to convert this to a narrow-long table like Colin describes. (That could be done in either Excel or Access. If the column can be incremented or read programmatically I would use Access; otherwise I would use a copy & paste routine in Excel.)

Below are the manual steps for a much smaller number of columns.
----

If you want to convert your data into the table Colin describes you can use a Make Table query.

For example:
Select tbl_Excel and the field
Item
Style_A
Amounts

Append To tbl_Access
Item
Style
Amounts

Run that query, then edit the query so instead of selecting Style_A you select Style_B
Repeat for all the styles.

Then with a Long-Narrow table (Item, Style, Amounts) it is much easier to work in Access.
To produce the output so it looks like your original table use a Cross-Tab query.

[orange]

If your Prices can change, you may want to design for that. You can not rely on the "current price" of the Item/Style, if/when you have to do any historical reporting or adjust any orders/invoices where there has been a Price change.

Do your analysis and design before jumping too quickly into physical database.
I agree with the other comments you have received, but I don't think you have fully described your requirement.
Good luck.