It was suggested I learn more on normalizing to reduce the headaches in my report which is generated from tables. I went thru and trimmed some fat and came up with 2 tables as listed below:

tbl_ShiftNumbers:
Date
CustomerCount
ActualHours
LaborDollars
NetSales

tbl_DailyResults:
Date
ActualSales
CashOverShort
Deposit
VerificationCode
DineSales
DineCC
WindowSales
WindowCC
TakeSales
TakeCC
MealPeriod1Sales
MealPeriod1CC
MealPeriod2Sales
MealPeriod2CC
MealPeriod3Sales
MealPeriod31CC
MealPeriod4Sales
MealPeriod4CC
ForecastCC
ActualHours
LaborDollars
Coupons
PromoCompCount
PromoCompAmount
PolicsCount
PoliceAmount
DeletesCount
DeletesAmount
MgrMealsCount
MgrMealsAmount
EmpMealsCount
EmpMealsAmount

I know, we track a lot of numbers. What can I do to normalize these? Would there be any point? I'm just learning about normalization so your help is greatly appreciated! Thanx!

James:confused:

You ned to break down the information into groups of the same type of info (entities/tables). So you could have [Sales Information] in one table, [Shift Information] in another, hours?, labour? etc etc. Remember you will be creating a record in each of this tables, this needs to be identified uniquely so create a unique primary key field in each table. Eg Shift ID etc make these an autonumber. You also need to think about your relationships between tables.

Hope this helps

There's a good thread on normalisation on another forum: click here (http://www.accessvba.com/showthread.php?s=&threadid=2583)

Do NOT use reserved words such as Date as column names. You will end up with problems once you need to use VBA since Access sometimes has difficulty determining whether you are attempting to refer to YOUR Date or ITS Date.

Looks like your tables are summaries of some other data. Are you calculating the sums or is the user inputting them? If you have a method of calculating them, it would be preferable to storing them.

Look at your column names and notice the suffixes. That is an indication of a repeating group. You have 1-to-many relationships that you have "flattened" a la a spreadsheet. This style of table requires frequent maintenance as new items are added to the group and this of course causes changes to queries/forms/reports/code. It is much better to create a proper 1-to-many relationship. That way items are simply new rows which require NO db modifications.

I'm not going to attempt to sort out which fields should stay in tbl_DailyResults and which should be moved to the child table but the child table should look like:

tbl_SalesDetail
SalesID (autonumber pk)
ShiftNumbersID (foreign key to tbl_DailyResults)
SalesTypeID (foreign key to tbl_SalesType
SalesAmt
CCSale (Boolean to indicate that this amount is a CC amt)

tbl_SalesType:
SalesTypeID (autonumber pk)
SalesTypeDesc

Some SalesTypeDesc values would be:
DineSales
WindowSales
TakeSales


You will need similar tables to hold the counts.