Need to reference multiple fields in combo box or Form Button

[jackie]

Hi,
I have a form that has buttons that run macros based on queries. The queries
are based on 2 fields in a table. These buttons work find. For sales data, I
have a table in which the sales for each state are in separate fields. Theres
NYsales, CAsales,TXsales, etc. There is no all inclusive field called
'Sales". Is there any way to create another button on the form that will
bring up sales data for each state? If I cant do it that way I would like to
create a combo box where the user selects NYsales, and all of the information
from the sales table is shown.

I looked through the forums, and I've tried for a few days to come up with a
solution. I would apprecaite any help. Thanks.

 

[Scottgem (MVP)]

Hi,
I have a form that has buttons that run macros based on queries. The queries
are based on 2 fields in a table. These buttons work find. For sales data, I
have a table in which the sales for each state are in separate fields. Theres
NYsales, CAsales,TXsales, etc. There is no all inclusive field called
'Sales". Is there any way to create another button on the form that will
bring up sales data for each state? If I cant do it that way I would like to
create a combo box where the user selects NYsales, and all of the information
from the sales table is shown.

I looked through the forums, and I've tried for a few days to come up with a
solution. I would apprecaite any help. Thanks.

First you need to fix the structural problem in your database. You
should NOT have fields for each area's sales. This is known as a
repeating group and violates normalization rules. You should have a
table where you enter sales and have a field in that table to indicate
the area. You can use a crosstab to display sales by area.

You can then create a filter by area to only show sales for the selected
area.

Hope this helps,
Scott<>
Microsoft Access MVP 2007

 

[jackie]

Thanks for your reply. I wanted to try what you suggested, but I must be
doing something wrong.
I know the table structure is incorrect. My other tables have a field
called Profit Center and then there are fields called orders, or unfilled
orders. But the sales table (which is not mine) has NYsales, PAsales, etc.
How do I create a field "Profit Center" for NY and PA and relate it to the
sales data in NYsales and PAsales. I tried creating a table called "Profit
Center" in which had an autonumber field for ProfitCenterID, and then added
all of the states.

Then I added "ProfitCenterID" to the table with the sales data, and created
a relationship a one to many relationship, but I got no data back.

I thought about using an IIF statement to create a new field:
IIF PAsales = .....but that doesn't make sense because it's the field name I
need to change, not the values in the field.



What I want to do is move PAsales to a table that has the fields, "Profit
Center" for the location "PA", and "Sales" for the value in the field.

I know you said use a crosstab, but I'm not experienced enough to figure
this out.

Thanks.