easy Access problem...I think...relationships bad? 3

[BoyWonderDesigns]

Hi my first post here, so bear with me.

I am doing a small database for a friend and having a bit of trouble. It has been several years since I did any Access, and I am apparently more rusty than I thought!

I have a 4 tables:
1) Pickup info (Prim. Key: Autonumber): address, phone, contact name
2) Deliver info (Prim. Key: Autonumber): address, phone, contact name
3) Billing info (Prim. Key: Autonumber): address, phone, contact name
4) Work Order (Prim. Key: Autonumber. Also contains the foreign keys for each of the above): date, 'entered by' name, and comments

On a single form, I have subforms for each of the other 3 tables' data (all the address info, etc.) then the couple other fields for work order date, comments, etc.

When you pull up a new form sheet, it will only let me enter data in the subforms, not for the work order fields (and it won't increment the work order Autonumber key, which I assume is the main problem here). I know this a really simple solution, since I remember doing databases like this before, but just can't remember what to do with it.

The relationships are all one-to-many

What am i doing wrong here...please rake me over the coals! LOL

THANKS experts!

Happy holidays

 

[BoyWonderDesigns]

if anyone wants to see the file, just post a message and I will load it to a server and post a link for you

Thanks!

 

[Remou]

Did you create the main form using the wizards? If not, I suggest you try that.

 

[seaport]

BoyWonderDesigns,

Please post the link.

Seaport

 

[BoyWonderDesigns]

ok here it is:

http://www.boywonderdesigns.com/tek/jobs1c.mdb

 

[lespaul]

I would recommend the following table structure (read the fundamentals document link below to find out WHY I'm suggesting this):

WorkOrders
WorkOrderID
CreateDate

Contacts
ContactID
ContactName
Address
PhoneNumber

WorkOrderContacts
WorkOrderID
ContactID
ContactType (Pickup, Deliver or Billing)




Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[BoyWonderDesigns]

ok what good will that offer me to change it to that structure? The reason I structured it like I did is for ease of use. Elderly people will be using this so he wants it simple as humanly possible.

Each work order will have all 3 contact types, but there may be times when they are entering existing customer data, and don't want duplicates, of course...they want to be able to just pull the existing contact info up into the new work order.

Hope that clarifies some

JB

 

[lespaul]

Have you looked at the Northwind Database? The structure I suggested is the way to structure a many to many relationship. Many workorders contain many contacts; many contacts may have many workorders. It's the standard. Display of data and data entry are completely separate issue from data storage.

A table should contain information about a single entity (or object).

A workorder contains:

A workorder number, a person placing the order, items on the order, a date the order was created, who to bill the order to, what day the order was shipped.

If every workorder is going to have all three contact types then the following would work:

WorkOrders
WorkOrderID
PickUpID (FK - ContactID)
DeliverId (FK - ContactID)
BillingID (FK - ContactID)


Contacts
ContactID
Name
Address
PhoneNumber

But having three tables with identical information isn't good form.

 

[seaport]

I believe the database structure needs a re-design.

The rationale underlying the usage of mainform-subform is that one record in the table for the main form is linked to multiple record in the table for the subform. In your database, it is just the reverse. According to the relationship diagram, you tried to link multiple reocrd in the main form to one record in the subform.

Therefore, I recommending using the "Order_Number" from the Order table as the foreign key in other tables.

Seaport

 

[BoyWonderDesigns]

Ahhh Leslie, I think that was the way I was trying to go with it...I knew I hadn't set something up right though.

I think I will try the latter approach you mentioned:

WorkOrders
WorkOrderID
PickUpID (FK - ContactID)
DeliverId (FK - ContactID)
BillingID (FK - ContactID)


Contacts
ContactID
Name
Address
PhoneNumber

make it a many-to-many...then try the form wizard with the new layout...

any other suggestions I'm all ears!

THANKS!

 

[lespaul]

well, if you structure it like this, you won't have a many to many, you have a one to many. Glad to help and feel free to post back if you have any other questions!

Leslie

 

[BoyWonderDesigns]

ok so thinking ahead using that 2-table approach...how will this effect the form? How will they enter the 3 different sets of contact info?

I'm just trying to think it out first before I start the redesign

 

[BoyWonderDesigns]

also, since we now have 3 foreign ID keys, how do I relate Orders to the ContactID? I think I am overthinking this way too much...thanks for helping an old rusty programmer!

 

[lespaul]

How will they enter the 3 different sets of contact info?

If every workorder requires those entries then you have on the form:[tt]
Pickup Contact Delivery Contact Billing Contact
(list of all contacts) (list of all contacts) (list of all contacts)
[/tt]

how do I relate Orders to the ContactID

in a query you would join like this:

SELECT W.*, P.Name, D.Name, B.Name
FROM WorkOrders W
INNER JOIN Contacts P on P.ContactID = W.PickupID
INNER JOIN Contacts D on D.ContactID = W.DeliveryID
INNER JOIN Contacts B on B.ContactID = W.BillingID

 

[BoyWonderDesigns]

ok may need some help with that...I haven't done any SQL coding before

So I don't need to set up any relationships in the Relationships window in Access on my 2 tables?

 

[lespaul]

Sure you can, you'll need to drag the ContactID from the ContactTable to the WorkOrder table 3 times, once for Pickup, once for Delivery and once for Billing.

 

[BoyWonderDesigns]

ahh! Ok didn't know you could do that

 

[PHV]

Tip: Have 3 different instances of the ContactTable in the Relationships window ...

 

[BoyWonderDesigns]

http://www.boywonderdesigns.com/tek/jobs1d.mdb

when trying to enter a new set of form data, getting an error saying a record doesn't exist (blah blah blah)..what have I screwed up now?? should I not be using subforms on the main form for the 3 contact info sets?

sorry for all the NEWBIE type questions...I just want to be sure I get this right! Thanks again for everyones tips and help!

JB

 

[lespaul]

I wouldn't use subforms there....I would do what I showed above, a dropdown list that shows the company name. If you put a dropdown list on the form and follow the wizard. Now, what if the company doesn't exist? Add a button to 'Add new Contact' which allows the user to add a contact when entering a new workorder.