Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Join Date
    May 2005
    Posts
    54

    Unhappy Unanswered: Relationship Problems??



    I am creating a database in which right now I only have two tables. One table is Customer [information], the other is Trade References. I have 3 fields in the Customer table (Trade Reference 1, Trade Reference 2, and Trade Reference 3) that are pulled from the Trade References table (lookup).

    I then created a form where the user can select which trade references they would like to pull. Then I created a report based on the form. This is where I am encountering problems. On the report it lists the correct names for the trade references, but it lists the same phone number and fax for each one.

    I.E.

    Customer: Joe Blow
    Trade Reference 1: John's Shop Phone: 222-222-2222 Fax: 123-456-7890

    Trade Reference 2: Apples R'Us Phone: 222-222-2222 Fax: 123-456-7890

    and so on......

    I've tried changing the relationships and nothing seems to work.....I am sure the solution is fairly simple, but I can't see the trees through the woods at this point....

    Please help!!!!!
    Last edited by somthinboutmary; 04-26-07 at 11:50. Reason: Changing Title to be more specific

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    A couple of things
    1) Are you aware that your design violates first normal form? The below might look daunting but it should be the minimum you read before designing a database:
    http://r937.com/relational.html

    2) I am not certain what you mean. Do you know the difference between joins and relationships? Relationships affect the data you can put into tables. Joins affect the data you see when getting them out. Your problem appears to be one of display so I would expect us to be talking about joins rather than relationships.

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    May 2005
    Posts
    54
    I read the article, and as dry as it was I did learn a few things. But nothing in the article really helped me figure out what my problem is.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Good job on reading it - most people ignore the fundamentals

    It was not specifically related to your problem however as soon as you have columns called Trade Reference 1, Trade Reference 2 and Trade Reference 3 in a table you are likely going to get yourself into a whole load of trouble in the future. I am happy to expand if you like but the bit about first normal form is the relevent part.

    Anyway - point two was more germain to what you were asking. Are you talking about changing the relationships between the tables or joins in a query?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    May 2005
    Posts
    54
    I understand about the 1NF. So I ended up removing the Trade 1, Trade 2, etc.

    But now I'm not sure how to pull the information together. I think I'm even more confused. UGH

    I don't even know that it is a relationship or join issue now.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Post what you use to retrieve the data for your report (SQL, VBA) and we can start from there.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    May 2005
    Posts
    54
    I am more familiar with VBA (however, I am still a novice)

  8. #8
    Join Date
    May 2005
    Posts
    54
    I have started over with the database and am just back to having the two tables. So now I need to figure out what my next step is in order to be able to list the Customer and 3 trades (being looked up from the trade reference table)....any ideas?

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    So presumably you have a foreign key in your reference table (CustomerID, perhaps?)...
    If so, then you can use the query builder to pick out the data for you.
    George
    Home | Blog

  10. #10
    Join Date
    May 2005
    Posts
    54
    No. The fields I have in each table are as below:

    Customer Information Table
    Customer ID (primary key)
    Customer Name
    Address
    City
    State
    Zip
    Phone
    etc., etc., etc.

    Trade Reference Table
    Trade Reference Name (primary key)
    Trade Reference Phone
    Trade Reference Fax

  11. #11
    Join Date
    May 2005
    Posts
    54
    What I would like to do is have a form where the user can type in the customer information, and then select three separate trade references from dropdown boxes. And then I would like them to be able to print a report (Trade Reference Request) for each trade reference selected (a separate report for each trade reference) that can be printed and faxed.

  12. #12
    Join Date
    Dec 2004
    Posts
    35
    Would you like to save the information of what trade requests have been requested for each customer ? Or does it matter ?

    QT

  13. #13
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,423
    Provided Answers: 8
    Form What Im reading I 2 table

    I think the Trader could Customer
    and a customer could be a trader

    so

    [Customer]
    CustomerID
    ...
    ...
    ...


    [TradeTable]
    tradeID <= just a primerkey
    CustomerID <= then customer number
    Trader <= which is the CustomerID number form the Customertable
    productid
    datetimetraded
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  14. #14
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by somthinboutmary
    No. The fields I have in each table are as below:

    Customer Information Table
    Customer ID (primary key)
    Customer Name
    Address
    City
    State
    Zip
    Phone
    etc., etc., etc.

    Trade Reference Table
    Trade Reference Name (primary key)
    Trade Reference Phone
    Trade Reference Fax
    So you have no join/relationship between the two tables.
    That makes your job a lot more difficult - you need to re-read the normalisation and database design notes.
    George
    Home | Blog

  15. #15
    Join Date
    May 2005
    Posts
    54
    Quote Originally Posted by QTNM
    Would you like to save the information of what trade requests have been requested for each customer ? Or does it matter ?

    QT
    We could save them. It really doesn't matter....whichever would be easiest.

Posting Permissions

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