If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Help starting
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 |
#2
|
|||
|
|||
Help starting My two Cents
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 "mastermars" wrote: 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 |
Thread Tools | |
Display Modes | |
|
|