AARGGH!! How to store data - HEEELLLPP!

[Phlipper via AccessMonster.com]

Hi,

I have a database for my publishing company. We publish monthly magazines, 12
separate titles.

Currently my database stores the number of issues for an order by a number
(representing number of months) that is decreased by one each month via a
query. Clumsy and not good enough.

I want to input an order with a month of first issue (e.g. January), and
month of last issue (e.g. June)

I then want to store that order for each of the applicable months so that I
can run a report each month telling us which customers are in which magazines.


Now I'm confused as to how to store the data; should I create a table for
each month, and have the form append a new record for each table?

Or should I have a field for each month? If I go down this route, how do I
handle a single order for multiple titles that do not all run for the same
months?

As a beginner, I've been told to use as few tables as possible, but I'm
beginning to think that maybe I should give each title it's own table too.
Confused, please give me your thoughts, they'd be very much appreciated.

TIA

Phil

 

[Allen Browne]

Product table (one record for each mag. title), with ProductID primary key
(pk)

Issue table (one record for each issue of each mag.) Fields:
- ProductID tells which magazine title this is.
- IssueDate date/time field, e.g. 1/1/2009 for Jan 2009.
Use the combination of the 2 fields as the primary key (so it's guaranteed
unique.)

Client table (one record for each person), with ClientID pk.

Order table (one record for each order header), with fields:
- OrderID AutoNumber pk
- ClientID which client this order is for
- OrderDate Date/Time date the order was received.

OrderDetail table (one record for each mag requested in the order):
- ProductID which mag.
- FirstIssue date/time matches an IssueDate for the product.
- LastIssue date/time matches a later IssueDate for the product.
- Charge currency price paid for this mag subscription.

Now you can tell that the client should receive any IssueDate between
FirstIssue and LastIssue, for that ProductID. It's also easy to query when
the subscription runs out.

The suggestion to use 2 tables for client orders means that a client is able
to order multiple magazines in the one order.

 

[Fred]

Phil,

Although some important things will need to get clarified/decided before you
can make a plan that covers all of the key areas, I think that it's pretty
clear what you basic table structure should be:

Add in fields for all data that has a one-to-one relationship with each entity

table of Magazines (I.E. Magazine Titles)
PK = MagazineID (autonumber)

A table of Customers (anyone who has ever ordered)
PK = CustomerID (autonumber)

An "orders" table
PK = OrderID (autonumber)
FK: CustomerID (integer)

An "OrderItems" table Important: This is a table of instances of
ordering of one magazine for a contiguous set of months
PK: OrderItemID (autonumber)
FK: OrderID (integer)

Link all fields to their namesakes

- - - -

Two remaining big decisions are the fine points of specifying the starts and
ends of the the subscriptions. As you already know, your current method
(essentially just the current number of months still open) is really bad.

I guess that the possibilities for the new system are the one that you
mentioned (specify the start and end months) or one whihc wouls specify the
start month and the number of months of the order item. Either way, these
wills be fields in your order items stable.

The other decision is that you will have to decide how to define what the
"current moment" or "magazineissueInQuestiotn" is. Inevitably, this will get
compared to the subscription month data. While it might simply be the
current date, that will get complicated, as you might be dealing with the
March issue during February or even January, and then mailing at a different
time which is neither. Most likely you will instead define the "issue in
question" (e.g. the January 2010 issue of "Modern Beekeper" magazine")
regardless of when you are looking into or mailing it. And the person
seeking data, mailing labels etc. would define the "issue in quesiton" of
their inquiry.

 

[Fred]

Sorry, while it was sitting on my computer while I left it and and didn't it
was already answered.

 

[Phlipper via AccessMonster.com]

Thanks Allen,

Regarding the Issue table; would that mean that I would need to manually
populate the table with the date of each issue of each title for evermore, or
do you mean that it would be populated by the data from the order form (I
assume the latter)?

(Please bare with me, I'm an extremely novice user) If the Issue table is
storing the dates for FirstIssue and LastIssue, am I right in assuming that I
would set my reports for a specific issue to run through a combination of
query and code that calculates whether a customer should be included in that
issue - rather than actually creating a record for those 'in between' issues?

Phil

Product table (one record for each mag. title), with ProductID primary key
(pk)

Issue table (one record for each issue of each mag.) Fields:
- ProductID tells which magazine title this is.
- IssueDate date/time field, e.g. 1/1/2009 for Jan 2009.
Use the combination of the 2 fields as the primary key (so it's guaranteed
unique.)

Client table (one record for each person), with ClientID pk.

Order table (one record for each order header), with fields:
- OrderID AutoNumber pk
- ClientID which client this order is for
- OrderDate Date/Time date the order was received.

OrderDetail table (one record for each mag requested in the order):
- ProductID which mag.
- FirstIssue date/time matches an IssueDate for the product.
- LastIssue date/time matches a later IssueDate for the product.
- Charge currency price paid for this mag subscription.

Now you can tell that the client should receive any IssueDate between
FirstIssue and LastIssue, for that ProductID. It's also easy to query when
the subscription runs out.

The suggestion to use 2 tables for client orders means that a client is able
to order multiple magazines in the one order.

Hi,

[quoted text clipped - 28 lines]

Phil

 

[Allen Browne]

The more the merrier, Fred. I'm sure the OP will be glad to have a couple of
perspectives.

 

[Phlipper via AccessMonster.com]

Thanks Fred,

Some really interesting issues here:

The OrderItems table:
I really like the idea of just having the start month, and then number of
months. Are you saying then that this data should be used to automatically
generate a new record for each instance (presumably automatically
incrementing the month of each instance)?

Would this OrderItems table then record the data for ALL titles, or do you
mean to create identical tables for each mag title?

Thanks, Phil

 

[Allen Browne]

There are ways to populate the Issue table automatically when you add a new
mag. If you populate it with (say) 600 issues, that would cover the next 50
years. If you wish, I can explain how to create such an append query: let us
know if ALL your products will only ever be monthlies, of if you anticipate
the could be weeklies, quarterlies, etc.

When you are ready to send out a particular issue, you would create a query,
and type the date into the Field row, e.g.:
#1/1/2007#
Then in the Criteria row under this, enter:
Between [FirstIssue] And [LastIssue]
Specify the specific product if you wish.
The query then returns the people who should get that issue.


--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks Allen,

Regarding the Issue table; would that mean that I would need to manually
populate the table with the date of each issue of each title for evermore,
or
do you mean that it would be populated by the data from the order form (I
assume the latter)?

(Please bare with me, I'm an extremely novice user) If the Issue table is
storing the dates for FirstIssue and LastIssue, am I right in assuming
that I
would set my reports for a specific issue to run through a combination of
query and code that calculates whether a customer should be included in
that
issue - rather than actually creating a record for those 'in between'
issues?

Phil

Product table (one record for each mag. title), with ProductID primary key
(pk)

Issue table (one record for each issue of each mag.) Fields:
- ProductID tells which magazine title this is.
- IssueDate date/time field, e.g. 1/1/2009 for Jan 2009.
Use the combination of the 2 fields as the primary key (so it's guaranteed
unique.)

Client table (one record for each person), with ClientID pk.

Order table (one record for each order header), with fields:
- OrderID AutoNumber pk
- ClientID which client this order is for
- OrderDate Date/Time date the order was received.

OrderDetail table (one record for each mag requested in the order):
- ProductID which mag.
- FirstIssue date/time matches an IssueDate for the product.
- LastIssue date/time matches a later IssueDate for the product.
- Charge currency price paid for this mag subscription.

Now you can tell that the client should receive any IssueDate between
FirstIssue and LastIssue, for that ProductID. It's also easy to query when
the subscription runs out.

The suggestion to use 2 tables for client orders means that a client is
able
to order multiple magazines in the one order.

Hi,

[quoted text clipped - 28 lines]

Phil

 

[Jeff Boyce]

To add to Allen's & Fred's excellent suggestions, I'll point out that "use
as few tables as possible" may have been well-intentioned, but not
appropriate. As a relational database, MS Access is optimized to work with
well-normalized data. That takes as many tables as it takes ...

If this is your first exposure to "normalization" and "relational", plan on
spending some time learning your way up this curve. Access will thank
you...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Phlipper via AccessMonster.com]

Just realised I may have led you all astray a little. The Customers/Clients
I'm referring to are not magazine subscriber, rather they are advertisers.
This is why I'm finding the whole thing so confusing;
A given advertiser may want to go into one title for 3 months, another for 6
etc. Or he may want to go into several titles for the same number of months,
but has already missed some of the issues - hence 'staggering' the order over
different months for each title.

 

[Phlipper via AccessMonster.com]

Hi Allen,

If you could explain how to write that append query, that would be great.

Many thanks

Phil

There are ways to populate the Issue table automatically when you add a new
mag. If you populate it with (say) 600 issues, that would cover the next 50
years. If you wish, I can explain how to create such an append query: let us
know if ALL your products will only ever be monthlies, of if you anticipate
the could be weeklies, quarterlies, etc.

When you are ready to send out a particular issue, you would create a query,
and type the date into the Field row, e.g.:
#1/1/2007#
Then in the Criteria row under this, enter:
Between [FirstIssue] And [LastIssue]
Specify the specific product if you wish.
The query then returns the people who should get that issue.

Thanks Allen,

[quoted text clipped - 50 lines]

 

[Fred]

Hello Phil,

Answering your quesitons....

Not sure what you meant by "instance". If, by that you mean an instance of
one ad being in one magazine for one month, for better or for worse my
structure does not have that. And so such a record would not exist or be
created. All answer are derived from looking at the purchased "block" of
ads in a particular magazine.

The one table would hold it for all of the magazines. The "ProductID"
field would identify which magazine the OrderItem is for.

Hope that helps a little.

 

[Allen Browne]

1. Create a table with just one field:
CountID Number
Mark it the primary key, and save the table as (say) tblCount.
Enter 12 records -- the numbers 0 to 11.
Close.

2. Create a new query using this table.
In the first column of the Field row, enter:
ProductID: 99
In the next column, enter:
IssueDate: DateAdd("m", [CountID], #1/1/2009#)
Test: you should see 12 rows, with 2 columns like this:
99 1/1/2009
99 2/1/2009
99 3/1/2009
and so on.

3. Change the query to an Append query (Append on the query menu/ribbon.)
Access asks what table you want to append to: it's the Issue table. You now
have a new row showing in the query design grid, and it should map the
ProductID and IssueDate columns to the matching fields in the Issue table.

4. At this point, Access has build a sample query for you. To see it, switch
to SQL View (View menu.) It will look like this:
INSERT INTO ...
SELECT ...
FROM ...
You need a string like this in your VBA code. Instead of the 99, we will
concatenate the actual product number into the string. Instead of the
1/1/2009, we will concatenate the first date into the string.

5. Ultimately, your code will execute this string, like this:
strSql = "INSERT INTO ... " & Me![ProductID] & " AS ProductID, ...
With dbEngine(0)(0)
.Execute strSql, dbFailOnError
MsgBox .RecordsAffected & " issue(s) appended."
End With

More info about execute:
http://allenbrowne.com/ser-60.html

If you end up doing this often, this utility helps get the sample SQL
statement from the utility into your code:
http://allenbrowne.com/ser-71.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi Allen,

If you could explain how to write that append query, that would be great.

Many thanks

Phil

There are ways to populate the Issue table automatically when you add a
new
mag. If you populate it with (say) 600 issues, that would cover the next
50
years. If you wish, I can explain how to create such an append query: let
us
know if ALL your products will only ever be monthlies, of if you
anticipate
the could be weeklies, quarterlies, etc.

When you are ready to send out a particular issue, you would create a
query,
and type the date into the Field row, e.g.:
#1/1/2007#
Then in the Criteria row under this, enter:
Between [FirstIssue] And [LastIssue]
Specify the specific product if you wish.
The query then returns the people who should get that issue.

Thanks Allen,

[quoted text clipped - 50 lines]

Phil