Best way to design this small database

[Angiec]

Hi
Its been many years since I used Access and I have been given the task of creating a small order/ tracking Database but not sure the best way to create it. The data base needs to hold the following information:

Customer contact information

Line order information - for customers who dont already have specific lines, we will order them so need information such as telephone number, post code, street information

Service Order - customers order information, this will hold order information including the telephone number, post code, street information etc - which if they have had to order a "line " through us will be the same information as in the line order information or if they already have a line then we will input this information manually

Fault Log - for customers who have ordered the service to log faults - will need to reference the telephone number, street information etc from the Service Order

So my question is based around best practice. I understand that databases shouldnt be duplicating data, so I dont want Line Order Table, Service Order Table and Fault Log with the same fields and the data input 3 times - whats the best way to do it? I did try making the tables and using lookup fields within the table which seemed to work to an extent until I ran a query and it duplicated the results.

TIA for your help :-)

[June7]

Ran what query? Post the SQL statement. What do you mean by 'duplicated'? What results are duplicated? I suspect the query is correct because it is likely a join of tables with 1-to-many relationship.

Here is good tutorial site http://www.rogersaccesslibrary.com/

If you want to post your attempted db, will review. Follow instructions at bottom of my post.

[RuralGuy]

I would start by Googling for MS Access Templates. I suspect you can find one that closely covers your requirements and then modify it to suit your needs more precisely.

[Angiec]

Hi
Thank you for your response, I have had a look at the suggested website, but I am not really sure where to start.

I have attached the database that I have created and I know its completely wrong.

Customer - contains the customer account name, contact name and their contact telephone

PTSN - When a customer orders an ADSL line from us they will either provide us with their existing telephone number, postcode and street information or if they dont have that then we will order one for them. In this database I have created a table for the ordering of new PTSN numbers.

ADSL - The customers order of an ADSL line

Fault Log - errors on the ADSL Line once its installed

Now I can already see where my faults lie - as there is way too much duplication because I have just used lookup drop down combo boxes to bring the information through into each table - which is just ridiculous.

Should I have more tables ? 1 for us ordering new PTSN Lines when required and then another table that contains the PTSN line information of those customers who already have the line? Then the ADSL Orders will be just the order information - I would then need to link all 3 tables together so that I can input into a form the ADSL Order and then either their existing PTSN Line info or the order information of a new PTSN line.

Then a separate table for faults which links to the ADSL Table.

I am sorry if this is not in the remit of this forum, but I am completely stuck as to how this should be designed

Thank you for your help :-)

Database for AC.zip

[ssanfu]

making the tables and using lookup fields

You might want to read

The Evils of Lookup Fields in Tables

http://access.mvps.org/access/lookupfields.htm

[Angiec]

Thank you :-) yes I thought that was the case I had a stupid moment

You might want to read

The Evils of Lookup Fields in Tables

http://access.mvps.org/access/lookupfields.htm

[June7]

If you mean the Roger Carlson site, perhaps the link called "Tutorials" would be good place to start.

Why do you have the ADSL table in Relationships 6 times? And PSTNLines 4 times?

Why are you saving ADSL ID into TelephoneNumber field?

The linking of ADSL, Customers, FaultLog is probably not appropriate.

You need to identify data entities and relationships. Ask questions like: Can each ADSL be associated with more than one customer? More than one Fault? Will each fault associate with only one ADSL? Faults associate with ADSL, not customer?

You have a query called FaultLogQuery but it has only the ADSL table. Not seeing any query as described in your post.

There are many sample dbs available from MS. From the Access app click on File > New > navigate the folders - try the one called Business

Advise no spaces or special characters/punctuation (underscore is exception) in naming convention.