AARGGH!! How to store data - HEEELLLPP!

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

--
Message posted via http://www.accessmonster.com

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"Phlipper via AccessMonster.com" u53406@uwe wrote in message
news:9b2bab7d8f9ce@uwe...
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

--
Message posted via http://www.accessmonster.com

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.

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

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

Allen Browne wrote:
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

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/200908/1

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"Fred" wrote in message
...
Sorry, while it was sitting on my computer while I left it and and didn't
it
was already answered.

Thanks Fred,

Some really interesting issues he

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

Fred wrote:
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.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/200908/1

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
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"Phlipper via AccessMonster.com" u53406@uwe wrote in message
news:9b2d0326f4bfa@uwe...
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

Allen Browne wrote:
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

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/200908/1

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" u53406@uwe wrote in message
news:9b2bab7d8f9ce@uwe...
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

--
Message posted via http://www.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.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/200908/1