Results 1 to 13 of 13
  1. #1
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Take a look, Im confused (Access2000)

    OK, heres my problem: I have 2 tables, TBL1 and TBL2. TBL2 has data related to TBL1. TBL1 uses TBL2 as a lookup table, but in some fields of TBL1 are empty so when I try to link TBL1 with TBL2 in query view I

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Take a look, Im confused (Access2000)

    If you actually have an empty record, there's something amiss with your design. What exactly do you mean by "use TBL2 as a lookup table"? Normally under those circumstances, you don't permit empty fields on the other side.
    Charlotte

  3. #3
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Take a look, Im confused (Access2000)

    I haven't designed the tables myself. I'm using these tables to create a amall program which allows the user to create reports by simply pressing a button. The input screen of the program where you input the value of TBL2 is a lookup but somehow the user must have entered data with empty feilds and it's giving me headache now. What can I do? PLEASE PLEASE HELP........

  4. #4
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Take a look, Im confused (Access2000)

    Perhaps it is the way your are explaining it, but something doesn't sound right here. YOu say Tbl2 has data related to Tbl1, but that Tbl1 uses Tbl2 as a lookup. That last statement implies that there Tbl2 is the "one" side of a one-to-many relationship with Tbl1. So why would it have related data? The data fields in Tbl2 should related to it's primary key, and not the data in Tbl1. And I don' t understand when you say you are losing data if some fields in Tbl1 are blank. You aren't losing the data, there is no data there.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  5. #5
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Take a look, Im confused (Access2000)

    SORRY to confuse you guys,
    Let me start again.
    User entry is stored in TBL1. TBL2 already has data which is used by TBL1 (TBL2 contains brokers information). When the user enters data they could select the broker in the input screen. But in TBL1 there are some empty feilds where the broker data should be. Im using these tables to display information on a report. When I try to link TBL1 to TBL2 to show details about the broker, the query isn't selecting all the records from TBL1 because of the empty feilds.
    Do I make sense now?

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Take a look, Im confused (Access2000)

    Hi,
    If I understand you correctly, I think you need to change your join properties between the two tables to "Include ALL records from TBL1 and only those records from TBL2 where the joined fields are equal". To do this, open your query in design view, right-click on the join and select 'join properties'. This way, even if your BrokerID (or whatever it's called) is missing from a record in TBL1, you will still retrieve that record.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Take a look, Im confused (Access2000)

    Oh, Thank you sooooooo much Rory, Thats what I wanted to know. <img src=/S/bow.gif border=0 alt=bow width=15 height=15>
    I tried to link this to a mail merged document from word (coz when I link it from access, it opens instance of access) it gives me an error message saying that word cannot open data source. Why is this? <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

  8. #8
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Take a look, Im confused (Access2000)

    >>Do I make sense now?<<

    No! You should not be storing ANY broker information in Tbl1 other than the broker ID in Tbl1!!!! I suspect you should have enforced referential integrity between the broker table and tbl1 such that you can't enter a record in Tbl1 until you have a broker record.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  9. #9
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Take a look, Im confused (Access2000)

    I repeat I did not design these tables and am restricted to see the design of the table. I'm only using it as a data source for the reports Im creating and YES, the link between two tables are the broker ID and I don't have a clue on how the user was allowed to leave the feilds for the broker ID empty.

  10. #10
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Take a look, Im confused (Access2000)

    If one of your tables contains broker information and the other only has a broker ID field, use an append query to append a record to the broker table that has a broker ID of 0 ( you can do this even if the key field is an autonumber). The replace the null values in the other table with 0 using an update query with Is Null as the criteria for that field. That will give you a link for those missing broker IDs.
    Charlotte

  11. #11
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Take a look, Im confused (Access2000)

    Thank you guys.

  12. #12
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Take a look, Im confused (Access2000)

    The only way around this is to use an outer join between the 2 tables. The usual Inner Join retrieves records from Table1 only if there is a matching record in Table2. You want to retrieve ALL records from Table1, and any related records from Table2.

    With your query opened in design view. right click on the relationship line between the 2 tables, then select properties. Then choose which relationship you want.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  13. #13
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Take a look, Im confused (Access2000)

    Thank you mark, I've done that already. <img src=/S/bow.gif border=0 alt=bow width=15 height=15>

Posting Permissions

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