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
|
|||
|
|||
Database/Query Help
I have built a table that contains monthly sales totals
from each our 52 stores. I have stores as rows and months as columns. Ex: Months Store#/Acct Jan Feb Mar Apr May Jun Jul 1000 Retail 1000 Contract 1000 Commercial 2000 Retail 2000 Contract 2000 Commercial 3000 Retail 3000 Contract 3000 Commercial I have a query that allows the user to select the data for a specific store, but I am having trouble finding a way to allow the user to select a month. Maybe I need to layout my table differently. Any guidance is appreciated. Ted |
#2
|
|||
|
|||
Database/Query Help
Yes, I would redo your layout. I would have one table:
StoreNumber AccountNumber Month Sales Now, you can get the totals by store and month very easily. Also, you can use a crosstab query to view the data the way you have it currently. Chris Nebinger -----Original Message----- I have built a table that contains monthly sales totals from each our 52 stores. I have stores as rows and months as columns. Ex: Months Store#/Acct Jan Feb Mar Apr May Jun Jul 1000 Retail 1000 Contract 1000 Commercial 2000 Retail 2000 Contract 2000 Commercial 3000 Retail 3000 Contract 3000 Commercial I have a query that allows the user to select the data for a specific store, but I am having trouble finding a way to allow the user to select a month. Maybe I need to layout my table differently. Any guidance is appreciated. Ted . |
#3
|
|||
|
|||
Database/Query Help
As far as I can see from your Table Structure, you have One Field in your
Table for Store#, One Field for Acct, and 7 Fields for the months. Each Field represents a column in your Datasheet Veiw... First, your table desing is not Normalized. You have lots of duplicate data in your table. I suggest you move the Acct to a new table, called tblAcct, the Months to a table tblMonths, and make the Store# Field Autonumber and Primary Key for your main table. In tblAcct, add two fields: AcctID, which is Autonumber and Primary Key, and Acct, in which you can store Retail, Contract, Commercial. In tblMonths make the same thing, but here name the fields MonthID, and Months. Then use Lookup wizard to "Look up" the data from tblAcct and tblMonths into your primary table, tblMain. And then you can build your query. -- Marin Kostov Microsoft Office XP Master Instructor |
Thread Tools | |
Display Modes | |
|
|