1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Help with realationships in access 2003

Discussion in 'Business Applications' started by karlhaywood, Jan 22, 2011.

Thread Status:
Not open for further replies.
Advertisement
  1. karlhaywood

    karlhaywood Account Closed Thread Starter

    Joined:
    Jan 17, 2010
    Messages:
    680
    Hi, i have created three tables.

    1. Clients table

    ClientID, Client Name, Add1, Add2, City, Postcode

    2. Jobs Table

    JobID, ClientID, Works Description, Date Received

    3. Invoices Table

    InvoiceID, ClientID, JobID, Subtotal, Vat, Total

    Should the data type be "Number" For the ID fields? How should the joins be in the relationships? Also any other info on the deign would be brilliant.

    Thanks for your time!
     
  2. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Karl, I would have
    Client Table, ClientID as Autonumber
    Jobs Table, JobsID as Autonumber ( I don't think you need ClientID in this table)
    Invoices table InvoiceID as Autonumber, ClientID and JobID as Number type long.
    The invoices table brings together the Client & Job and therfore should be One to Many from the Clint & Job to the Invoice.
    Can you have more than one job per Client Invoice?
     
  3. karlhaywood

    karlhaywood Account Closed Thread Starter

    Joined:
    Jan 17, 2010
    Messages:
    680
    Yes the clients will have more than one job, but each job will be invoiced separately. Could you check the attached screenshot to see if the relationships are correct?
     

    Attached Files:

  4. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Then that begs the question, do you need an Invoice Table at all?
    If you add the invoice fields to the Job table it should still work.
     
  5. karlhaywood

    karlhaywood Account Closed Thread Starter

    Joined:
    Jan 17, 2010
    Messages:
    680
    I would like to have the invoice table, so we can always look back and maybe one day clients will have more than 1 job on an invoice. Can you help me with the forms? I have never created forms off related tables.
     
  6. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    I would first of all create a form based on a Customer Query for the Customers with an added Tab control.
    Have the Customer ID on the mainform along with a Find Combo.
    On one of the tabs have the rest of the Customer's details.
    Then create a query based on the other 2 tables and ensure that it's recordset is updatable.
    Providing it is updatabale, create a form (for use a subform) based on the query, if you want to see more than 1 Job/Invoice ata a time set it to Continuous Forms mode. The Invoice table JobID will have to be updated using VBA code when the Job Received field is filled in.

    I don't think you need the Client's name in the Jobs table as the Invoice links the 2 together.

    You could also arrange the tables so that the CustomerID goes in to the Jobs table (as you had it) and only the JobID goes in the Invoice table.I suggest you try the many to many table first, although your Relationships have not set up Referential Intgerity.
    Add the new subform to the Customer mainform on the unused Tab page linking via CustomerID.
     
  7. karlhaywood

    karlhaywood Account Closed Thread Starter

    Joined:
    Jan 17, 2010
    Messages:
    680
    How would i find out that the recordset is updateable? also in the query do i have to have a join line between the 2 tables? Could you check the attached to see if this is correct? i dont know what fields to put in the query.
     

    Attached Files:

    • OBP.zip
      File size:
      24.6 KB
      Views:
      9
  8. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    To check if a recordset is updatable just try and enter some data in to the query and it is. It should have all the fields from both tables but I don't think you will be able to have a Continuous form with so many fields though.
    Should your Customer table have Phone numbers, email etc?
     

    Attached Files:

    • OBP.zip
      File size:
      28.2 KB
      Views:
      7
  9. karlhaywood

    karlhaywood Account Closed Thread Starter

    Joined:
    Jan 17, 2010
    Messages:
    680
    OBP could you check the form FrmJobs, i dont think i have created it properly, because it has a datasheet subform.
     

    Attached Files:

    • OBP.zip
      File size:
      33.8 KB
      Views:
      10
  10. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Karl, if you used the wizard to create the form it does that because the query has 2 tables.
    Instead create the form using "Create form in Design view", add the query as the recordsource and then use the Field List to drag all the fields on to the form, but as I said you will need to populate the JobID using VBA
    The way that the wizard designed it is how it should be when using a Form/Subform and you can do it that way if you want becasue then you don't need the VBA for the JobID.
     
  11. karlhaywood

    karlhaywood Account Closed Thread Starter

    Joined:
    Jan 17, 2010
    Messages:
    680
    OK OBP i have added the query and dragged all the fields to the form. Now do i add that form to the spare tab?
     
  12. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Yes, link it via the CustomerIDs.
     
  13. karlhaywood

    karlhaywood Account Closed Thread Starter

    Joined:
    Jan 17, 2010
    Messages:
    680
    Ok ive linked the child fields to the ClientID and the master fields to the ClientID, is that correct? What next?
     
  14. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    What is the first Job field on the subform where you will enter data?
     
  15. karlhaywood

    karlhaywood Account Closed Thread Starter

    Joined:
    Jan 17, 2010
    Messages:
    680
    it is the jobID
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/976267

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice