Need help with basic setup

[Guest]

I want to set up a dbase that will keep track of comic subscribers at my
store. Here is the data that I need to include:
Comic Titles
Comic Company
Subscriber Name
Comics they subscribe to
Customer Phone#

I then need reports that will tell me:
Who subscribes to what comic?
How many subscribers that we have?

I hope you can help.

 

[Ed Warren]

First you have design the database.


(From experience we can say this list is probably incomplete. Any time
spent early in making sure all the required data is identified and properly
placed in the design is repaid many times over during the rest of the
development. Changing data design can become painful in the later stages of
development)
For example the concept of a 'subscription' with a start date,
expiration date, payments, items subscribed to seems missing.

From the data you have provided we can say:

Each Comic Company produces many Comic Titles
Each Subscriber subscribes to many Comics (Many:Many)
Each Comic has many subscribers (Many:Many)
Each Comic is published by one and only one Comic Company (1:Many)
Either
Case1: Each customer has many telephone numbers (home, office, cell,
fax, etc)
OR
Case2: Each customer has one and only one telephone number.

So you are going to have (at least) the following tables.

ComicTitles (TitleID (primary key), TitleText, other stuff about this
specific comic title)
ComicCompanies (CompanyID (primary key), CompanyText, Address, Other stuff
about this specific company)
ComicSubscribers (SubscriberID (primary key), SubscriberFirstName,
SubscriberLastName, Other stuff about this specific subscriber)
Case 1: Detail Table for Telephone numbers
ComicSubscriber_Telephone (TelephoneID, SubscriberID,
TelephoneType, TelephoneNumber)
Case 2: Add a field for telephone number

ComicSubscriber_Comics (SubscriberID, ComicID) -- A 'junction table' to
implement the Many to Many relationship between comics and subscribers
ComicCompany_Comics (CompanyID, ComicID) -- A detail table to implement the
1:M relationship between Companies and Comics

After you get the tables and relationships built you will need to build a
set of forms to enter the data. These will include sub-forms for the 1:m
relationships (see the help files).

Then you will build a set of queries to get the information you need in the
form/format required for your reports.

Then you will build the required reports you discuss.

Best of luck.

Ed Warren.

 

[Guest]

I have the tables setup. What is the best way to create a form to add
Subscribers and the comics that they subscribe to in an efficient way, where
I can add alot of comics to one subscriber.

 

[Ed Warren]

You will need to build a form for each of the tables (and input some data in
each)

the Form for ComicSubscribers will have a text field and a combobox the
text field will be linked to the subscriberID (for now leave it visible,
later you will want to make it not visible)

the ComboBox will contain the ComicID value and will use the Comics Table as
the lookup values (see the help documentation).
The Form ComicSubscribers will be place in the Subscriber form as a subform
(linked on the SubscriberID value)

Now (providing you have entered some data into the comics table) you are
ready to enter as many comics as you will for each subscriber.

Ed Warren.

 

[Guest]

Thank you, Ed. You have been so helpful.

I cannot seem to get the links to work. What help topic(s) will help me
learn how to use the combo box and get it to work.

 

[Ed Warren]

Take a look a the combobox documentation and/or the Northwind example.
These should help. Also go to Microsoft.com and do a search for MSAccess
combobox.


Ed Warren.

 

[Tammy]

I am looking for a database that has most issues already added... anyone know
of where I can find one?


Thanks,