Results 1 to 12 of 12
  1. #1
    Join Date
    Apr 2004
    Location
    USA California
    Posts
    7

    Unanswered: Multiple Tables Help!

    Hi

    Currently i'm developing a application for my own record keeping at home. I'm using Visual Basics .NET and Microsoft Access for this. This is for personal database purpose since i developed a interest for database, currently i have two tables in my database, but they are already giving me problems.

    Table: Clients & Invoice

    Clients
    -CustomerID (PK) (AutoNumber)
    -Name
    -Address
    -DateOfBirth
    -ContactNumber

    Invoice
    -ReferenceNo (PK)
    -CustomerID (AutoNumber)
    -Date


    I realise for both CustomerID to have Relationship with each other, they must contain the same datatype. I alos want my customerID in Clients table to be EQUALs to Invoice's CustomerID...

    thus i don't know whether in my visual basics, should i add in INSERT INTO (bothTables) or just into my client tablE?

  2. #2
    Join Date
    Aug 2003
    Location
    Argentina
    Posts
    465

    Re: Multiple Tables Help!

    Originally posted by dEEM
    Hi

    Currently i'm developing a application for my own record keeping at home. I'm using Visual Basics .NET and Microsoft Access for this. This is for personal database purpose since i developed a interest for database, currently i have two tables in my database, but they are already giving me problems.

    Table: Clients & Invoice

    Clients
    -CustomerID (PK) (AutoNumber)
    -Name
    -Address
    -DateOfBirth
    -ContactNumber

    Invoice
    -ReferenceNo (PK)
    -CustomerID (AutoNumber)
    -Date


    I realise for both CustomerID to have Relationship with each other, they must contain the same datatype. I alos want my customerID in Clients table to be EQUALs to Invoice's CustomerID...

    thus i don't know whether in my visual basics, should i add in INSERT INTO (bothTables) or just into my client tablE?
    First in the table Client make a primaryKey with CustomerID and in the table Invoice make a primaryKey by ReferenceNo and CustomerID.
    Second with the tool Relations in Access link the table Client with the table Invoice by field CustomerID.
    When you add record to the Invoice table the field CustomerID must take the data from a combo box whose record source is a query of the table Client.
    Saludos
    Norberto

  3. #3
    Join Date
    Apr 2004
    Location
    USA California
    Posts
    7
    ok i did what u said, except i dont think i can add two primary keys.

    currently i have...

    Customer.CustomerID(PK) = Invoice.CustomerID (ONE-TO-MANY; ONE CUSTOMER, MANY INVOICE RECEIPTS)


    Invoice.ReferenceNo (PK)


    but it still does not answer my question...


    thanks for helping...

  4. #4
    Join Date
    Apr 2004
    Location
    USA California
    Posts
    7
    another thing i want to add is, the (AutoNumber) in the datatype. I can't seem to make both tables' CustomerID EQUAL to each other because they have records before...

    if i edit Invoice, and add a new row, it will prompt me, "You cannot add or change a record because a related record is required in table 'Customer'."



    my access database is here..
    http://alt.chronoscape.net/members/db.mdb
    Last edited by dEEM; 04-03-04 at 07:29.

  5. #5
    Join Date
    Aug 2003
    Location
    Argentina
    Posts
    465
    Originally posted by dEEM
    another thing i want to add is, the (AutoNumber) in the datatype. I can't seem to make both tables' CustomerID EQUAL to each other because they have records before...

    if i edit Invoice, and add a new row, it will prompt me, "You cannot add or change a record because a related record is required in table 'Customer'."



    my access database is here..
    http://alt.chronoscape.net/members/db.mdb
    Ok ..
    I can see your base because i have Access 97, them convert your base to Access 97 with the tools menu, put the sample base in a reply option attach file y make the change and send to you ok..in a reply
    Saludos
    Norberto

  6. #6
    Join Date
    Apr 2004
    Location
    USA California
    Posts
    7
    same link chagned to 1997

  7. #7
    Join Date
    Aug 2003
    Location
    Argentina
    Posts
    465
    Originally posted by dEEM
    same link chagned to 1997
    I send your base with the update see the tables design and see the sample form1.
    I hope this help you
    Attached Files Attached Files
    Saludos
    Norberto

  8. #8
    Join Date
    Apr 2004
    Location
    USA California
    Posts
    7
    i think u got the wrong idea. and im sorry.

    what i wanted was whenever i do a INSERT statement from my external program, i would like the CustomerID in the Invoice table to reflect as well.

    ill upload my program and show u...

    thsi is done in visual studio .NET and microsoft access 2003
    Attached Files Attached Files

  9. #9
    Join Date
    Aug 2003
    Location
    Argentina
    Posts
    465
    Sorry but i don't have visual estudio. net and i have Access 97 that why
    i can't open your base for see what do you want.
    Saludos
    Norberto

  10. #10
    Join Date
    Apr 2004
    Location
    USA California
    Posts
    7

    Post

    thanks..

  11. #11
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    dEEM,

    This is what you say you have:

    Clients
    -CustomerID (PK) (AutoNumber)
    -Name
    -Address
    -DateOfBirth
    -ContactNumber

    Invoice
    -ReferenceNo (PK)
    -CustomerID (AutoNumber)
    -Date

    This is what you should have:

    Clients
    -CustomerID (PK) (AutoNumber)
    -Name
    -Address
    -DateOfBirth
    -ContactNumber

    Invoice
    -ReferenceNo (PK) (AutoNumber)
    -CustomerID (Number--Long)
    -Date

    This is assuming that you don't mind the system assigning Customer numbers for you (that is a function of the Autonumber) and you don't mind the system assigning InvoiceReferenceNumbers for you (also a function of the Autonumber)

    As far as references go, References have their idiosyncracies, so I never use that function and maintain referencial integrity using my own code.

    As far as updating both table, you just need to run two SQL statements, one right after the other.
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  12. #12
    Join Date
    Apr 2004
    Location
    USA California
    Posts
    7
    thanks guys!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •