Related Tables

[roybb]

Hi Everyone,

I'm fairly new to access. What I am trying to do is create a database for keeping track of minutes remaining on support contracts (for example a customer initially purchased 90 minutes of support and then used 15 minutes of that contract).

I want to have 2 tables.

The first one is a general view of the individual customers and will include:
- Customer No (AutoNumber)
- Last Name
- First Name
- If they have an active contract
- Time remaining on their current contract and the date their last contract was purchased

The second table will be a log of the individual sessions for each customer and will include:
- Customer No
- Date
- Duration of contracted "used up"
- Description

I have made Customer No in the first table a primary key and made it a foreign key in the second table (one to many).

I am having trouble setting up the second table so that I can log multiple sessions for the same Customer No (due to Customer No being an AutoNumber). What I would ultimately like to do with the second table is to be able to print out a list with a log of an individual customer's sessions.



I am really not sure how to approach this?

Thanks!

Roy

[ssanfu]

In the second table, "Customer No" should be a Long, not an Autonumber.


This is how I would begin. I see 3 tables:

Table:  Customers
----------------
CustomerID_PK   Autonumber
CustomerNum     Text (optional - user assigned)
FirstName       Text
LastName        Text
'Other customer data: Address, cellPh, Email, etc


Table: Contracts
----------------
ContractID_PK   Autonumber
CustomerID_FK   Long
EventTypeID_FK    Long  
EventDate       Date/Time
Amount          Long (if using only whole minutes)
Description     Text (or Memo)


Table: EventType
----------------
EventTypeID_PK    Autonumber
Description     Text

To begin with, the EventType table entries would be "Purchased" and "Used".
"Purchased" would be a positive number and "Used" would be negative. Adding the "Purchased" and "Used" would give total minutes left.
You could use the EventDate field to see a running history of "Purchased" and "Used" time.
Other events could also be added: "Voided", "Adjustments", "Refund".

The fields with the suffix "_PK" or "_FK" should never be displayed on a for/report. They are to provide a unique record identifier and for linking purposes.


Hopefully, others with more design experience will chime in.



You might want to see these tutorials:
http://www.rogersaccesslibrary.com/forum/forum46.html

[June7]

This is like inventory tracking. Sum total purchased minus Sum total used = balance.

Review: http://allenbrowne.com/AppInventory.html

Look at the Microsoft Desktop Product Inventory database template for ideas.

I expect you will need more than 2 tables.

[roybb]

Hi,

Thank you for your reply!

I definitely see why the CustomerID_FK in the second table needs to be a Long but I am unable to change it from AutoNumber as FM gives me a warning that I can not change the type because of the relationship it has with the other table.

Can anyone shine some light on this??

Thanks!

[June7]

Just changing the type won't be useful. Need a new field for the customer ID foreign key. Then need to populate that field with the CustomerID value from Customers table.

Can you just delete records from second table and start over?

Then remove relationships from Relationship window. Create the new field, delete the old autonumber field. Reset relationship with the new FK field.

[roybb]

I got it figured out, thanks so much!