If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Need to reference multiple fields in combo box or Form Button
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. |
#2
|
|||
|
|||
Need to reference multiple fields in combo box or Form Button
jackie wrote:
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 |
#3
|
|||
|
|||
Need to reference multiple fields in combo box or Form Button
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. "Scottgem (MVP)" wrote: jackie wrote: 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 |
Thread Tools | |
Display Modes | |
|
|