Which is better one long list or the alternatives?

[Guest]

I am making a database that is for doing audits of sales figures. I get
sales information (usually for 12 monthly figures, but sometimes one figure
for a year) from a large number of different divisions and classifications.
I was thinking that I would use one table to store the information about the
divisions and classifications, and then another to store the sales
information. The sales information table would have a foreign key that would
be the division/class id, the month, the year, then the sales value. When I
wanted to print out the audit I would have either a form or a report that
would gather the division information and all the sales figures with the
division id, which I could then print out.

Is this a good way to go about doing this? The sales table is going to get
very long (at least 24,000 records will be added each year) and I was
wondering if this would slow things down too much when I try to access the
information.

In a related question, is there an easy way to automatically generate the
months and years in the sales table based on a start and end date that would
be entered in another field (perhaps in the company information table)? Or
to sum the information once it is collected?

I appreciate any help I can get with this, right now I'm doing it with Excel
and I'm spending a lot of time entering duplicate information in different
spreadsheets.

 

[Guest]

I should have also mentioned that the time periods don't all start in the
same month. For instance, this month I get a batch that I need sales from
August 2004 to July 2005, but next month I would need to get sales from
September 2004 to August 2005. There is also the possibility that I may need
to gather sales for a period longer than 12 months at a time. So another
division might be January 2004 through September 2005.

 

[Vincent Johns]

Relational databases are designed to be flexible, so there are many ways
to set up your data. What I'm about to say are suggestions only; use
them if they make sense to you.

Perhaps at least that many. Does a division share several
classifications? If so, then you might want to put information about
classifications into a [Classifications] Table and linking each record
to the appropriate division in the [Divisions] Table.

I assume you mean that the foreign key would identify the division and
class in the [Divisions and Classifications] Table. An easy way to do
this is to specify an Autonumber field or some (short) field with unique
values as the master key in [Divisions and Classifications], and use
that same value as the foreign key in your [Sales] Table.

You don't have to store month and year separately; they can be combined
into a field of Date/Time type. Functions are available to do
calculations on Date/Time fields, such as elapsed time between two such
values, the month number of a Date/Time value, the time of day, etc.

Sales value should probably be stored as a Currency type.

That's your goal, but I suggest that you first create Queries that will
produce the results you want. Then you can base your Forms (for data
entry) and Reports (for printed output) on these Queries.

It shouldn't give Access any trouble (but look up "Microsoft Access
specifications" in Access Help for specifics). I suggest you worry
first about developing a correct solution, then later about efficiency.
If efficiency becomes an issue later, you might consider switching to
SQL Server, but that hardly seems indicated at this point.

There are some obvious considerations, though. For example, if you
frequently look up values in a Table based on dates, then define an
index on that Table's date field. Don't define primary keys that are
100 bytes long -- 4 bytes will normally suffice. (Stuff like that.)

I think you want to avoid stuffing that into any Table. My guess is
that you want to examine the data pertaining to some time period. If
so, specify that time period in the Query that examines the data. This
could be a parameter in the Query, something like [Please enter the
starting date].

Although I think using Access would make your process easier (at least,
it would be easier after you have converted your system to Access), I
don't see why you have to enter any duplicate information. Why not just
include a formula reference instead of duplicating? You could protect
the cells containing those formulas to avoid accidentally changing them.

I should have also mentioned that the time periods don't all start in the
same month. For instance, this month I get a batch that I need sales from
August 2004 to July 2005, but next month I would need to get sales from
September 2004 to August 2005. There is also the possibility that I may need
to gather sales for a period longer than 12 months at a time. So another
division might be January 2004 through September 2005.

Your Query would include specifications for both start time and end time
for the data you want to examine. Or, if you frequently need a 12-month
period, you could create a copy of the Query that asks only for the
starting data and computes the ending date as 12 months later.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.