table design for home delivered meal ordes

[dwilson]

I am having trouble coming up with a design to convert an application from Excel to Access. Basically we have to keep track of meal orders from about 30 sites. In Excel there were spreadsheets for every month, with tabs for each week, and each spreadsheet shows Monday - Friday for that week. They need totals for every week. I was initially trying to set up the tables as
Octwk1p1, Octwk1p2, Octwk1p3, etc putting as many fields as I could in each table, and this was just for the month of October for week 1.
So my table design looked like:
Ahepa M
Ahepa R M


Ahepa NPP M
Ahepa 5P M
Ahepa 3P M
Ahepa 2
Ahepa PP M
Then the same rows for Tue, We,Th, F
Then I would move on to the next meal route, Barton
I know this is bad design because you are not supposed to have a lot of fields in an Access table. And I was going to have six or so tables just to handle one week of one month, and they each had close to their limit on the 255 fields. Then of course I hit the limit in the forms as well, trying to have calculated fields for the week.
I have attached an example of what a small section on the Excel spreadsheet looked like for two meal routes for one week in October.
I am now trying to figure out another design, but this issue is the data needs to be presented to the user in this type of format for input. Each week they need to call in their weekly totals.
The Excel spreadsheet worked but it was bunglesome, and so they asked me if I could come up with a way to do this in Access. My work in Access has always been more demographic data related, not numbers so I am kind of at a loss. Any help would be appreciate.

[June7]

Build normalized design. There are methods to manipulate the data for the output you show. A CROSSTAB query might be good enough or might require VBA code.

Maybe tutorials here will help you get a start http://www.rogersaccesslibrary.com/

[dwilson]

Thanks, yes I need to rethink how to desgn this in Access.