It is good to have a primary key on every table, your ServiceRecords
especially needs one.
You can't create one-to-many relationship between ServiceRecords and Parts -
it will not work. You need a many-to-many relationship here. You can't
create such relationships directly, but this is what cross tables are for.
Here's an Example:
---Table SR_Parts---
RecID Autonumber (PK)
PartID Number (Foreign Key - Parts)
SRID Number (FK - ServiceRecords)
---end---
Now suppose you want to see status of customer's order.
From the customers table you retrieve his/her id and then look at
ServiceRequest table.
Here you find all that customer's orders and list them to a user in a
subform (or use any other method of selection if you wish). The user
selects a record he/she is interested about and a moment later another list
is displayed with all parts that were needed to perform the job.
When the user selected a record from SR table, you have a SRID of that
record and search
SR_Parts table for that SRID and from all returned rows from SR_Parts you
get PartID's and query the Parts table for them. Simple ah? Actually it is
simplier than it sounds and usually is done with a single query like
SELECT SRID,PartID,PartNumber,PartDescription
FROM Parts p INNER JOIN SR_Parts ON Parts.PartID=SR_Parts.PartID
WHERE SRID=[YourForm].[YourControl]
You don't display SRID or PartID to the user, only the information you want
him to see,
PartNumber and PartDescription, for example
Should I have CustomerID as opposed to customer name?
What if you have two customers with a name John Doe? And numbers are faster
to search.
HTH,
Alex.
steveh said:
Thanks Alex!
I think I need to explain more what I am trying to acomplish. I want to be
able to lookup a serial number and see the service date, the serial number,
the customer, the technician who did the work, what type of repair was
performed and maybe even what parts were used. I also may want to see
service records based on a customer name. As a second part of this db, I
want to be able to use the db to manage our inventory of parts.
I am wondering about relationships. From what I have been reading
one-to-one relationships are not the way to go, but a one-to-many. How
important is it to keep in mind the relationship as I design my tables? Last
night I did some redesign on my service records table with the thought of
relationships in mind(see below)
----- Alex Ivanov wrote: -----
Steven,
Not bad for beginning. I think you will need more tables as your work
progresses, at least some I see missing now - "Categories", "Suppliers"
(Suppliers may also be the same as customers though).
~ I will add
I would also recommend adding autonumbers to your tables as primary keys.
You can then link the data easier. Add CustomerID to your ServiceRecords,
but remove PartsReplaced.
~I actually have PK on each table except service records(see below).
Should I have CustomerID as opposed to customer name?(see below)
You probably will need another table (cross table) containing both
ServiceRecordID's and PartID's as every service may require several parts
to be replaced.
~You are right each service will require several parts. What is a cross table?
Good luck,
Alex.
records on
equipment. My initial thoughts are to have the following tables
PartsReplaced(Combibox - lookup from parts table)
SerialNumber(Combibox - lookup from products table)
Technician(Combibox - lookup from technicians table)
Customer(Combibox - lookup from customers table)
I do not have a PK in this table so I can create a one-to-many
relationship with the table that hold the data. Right? Wrong?
I created a form from service table. On the serialnumber field I added a
event procedure to the notinlist to determine if the serial number exists
and if not add it. I coded the following from examples I found
Private Sub SerialNumber_NotInList(NewData As String, Response As Integer)
Dim ctl As Control
' Return Control object that points to combo box.
Set ctl = SerialNumber
' Prompt user to verify they wish to add new value.
If MsgBox("serial number is not in list. Do you want to add a new one?", vbOKCancel) = vbOK Then
Response = acDataErrContinue
' Open the Products Form'
DoCmd.OpenForm "FrmProducts", acNormal, , , acFormAdd, acWindowNormal
Else
'If user chooses Cancel, suppress error message
'and undo changes.
ctl.Undo
End If
End Sub
It seems to work so far. One problems I need to address. I want the data
on the service record form to populate after I key in the serial number(If
it exists). Not quite sure how to do that.
