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

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

.

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