Help starting

[mastermars]

Hello all,

I am trying to set up a data base and need some help sorry this may bit a bit
long winded.

We have around 70 mobile phones which are on loan to set customers and we
bill them for all calls made. Once a month I receive an excel file listing
all the call information.

I am trying to set up a Data base on Access that will create a invoice for
each mobile number listing all the calls between to set dates ( that I enter)
and costs.

This is how I started:

Table 1
Mobile number (key)
Customer (some customers have many phones)
( links the customer to a number)

Table 2
Customer (key)
Bill information
Address


Table 3 ( imported from Excel )
All call logs
I added a auto number field. (Key)


At the moment I have I have customer in table 1 linked to customer in table 2
and mobile number in Table 1 linked to Mobile Number in table 3

I seem to have problem adding a invoice details table and linking it in.

can any one help.

Many thanks
Sean

 

[Barry A&P]

Sean
Maybe You could look into

Tbl_Customers
CustomerID (pk) Autonumber?
FName
Lname
Address
City
State
Zip

Tbl_CallDetails (Appended from excell spreadsheet)
DetailID (PK) Auto Number
PhoneID (Maybe "PhoneNumber") not sure how bill references this
CallDate
NumberCalled?
CallCharge
InvoiceID (FK) Tbl_Invoices

Tbl_PhoneList
PhoneID (PK)
SerialNumber (Maybe ESN: i think this is what cell phones go by.)
PhoneNumber
CustomerID (FK)

Tbl_Invoices
InvoiceID (PK)
InvoiceNumber
InvoiceDate
CustomerID (FK)

You could use an "Append" query to Import your records from excell to your
Tbl_callDetails

temporarily you could generate a invoice report based on Tbl_calldetails.
group by customer and group by phone number Phonenumber then limit the dates

Then in the future you could use an "update" Query to add the Invoice
Numbers to the Tbl_calldetails with a isnull(InvoiceID) to keep from
billing a call twice all with code to explore in the future..

Just my two cents hope im not off base..
Barry