Results 1 to 15 of 15
  1. #1
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Missing field data (97)

    I've got two tables apparently in a simple one to many relationship. When I build a query and include fields from both tables information from a field on the one-side does not appear?! I just get a blank column. The one-table is also related to other tables, but these are not included in the query. Why would this happen??? Andy

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Missing field data (97)

    Hi Andy,

    Can you post the SQL for the query, and/or a screenshot of the query in design view?

  3. #3
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Missing field data (97)

    Will do, but I don't have it on me. Will post it tomorrow.

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

    Re: Missing field data (97)

    Andy,

    If this is related to the same question you asked in <!post=Post 246004,246004>Post 246004<!/post>, it would have been better to keep it all in one thread. Without knowing how the tables are related and joined and exactly which fields you're talking about, it isn't possible to answer the question.
    Charlotte

  5. #5
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Missing field data (97)

    This is related to the question in Post 246004, but I thought they would be two different problems. Here is the SQL:

    SELECT tblSingles.SingleID, tblSingles.TITLE, tblSingles.ARTIST, tblSingles.CD_CatalogueNumber, tblPublisherShares.Publisher, tblPublisherShares.Share
    FROM tblSingles INNER JOIN tblPublisherShares ON tblSingles.SingleID = tblPublisherShares.Song_ID;

    The field CD_CatalogueNumber shows as as blank column. If I can resolve this problem, I then have a third table called tblPositions that I want to include, which has the fields SingleID, Position, Period and Year. I hope that this makes a little more sense now?!

  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Missing field data (97)

    I know this is probably a silly question, but is there anything in the field tblSingles.CD_CatalogueNumber?

    Pat

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Missing field data (97)

    If SingleID, Title and Artist show something, there is no apparent reason why CD_CatalogueNumber from the same table would show up empty, unless there are no data in that field (as suggested by Pat), or if the name of the field is misspelled (but in that case, you'd get a parameter prompt).

  8. #8
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Leuven, Vlaanderen, Belgium
    Posts
    322
    Thanks
    9
    Thanked 0 Times in 0 Posts

    Re: Missing field data (97)

    Another possible silly question...
    How do you manage to join two tables by using different ID's (song <=> single)?
    In plain English: if I translate your SQL, I read: show all records for which the SONG shared by the publishers is the same as the SINGLE you talk about.
    If this is no mistake (and then, I still wonder <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29> how you could get a decent record set as a result of this query), didn't you forget a relation table SingleSongs somewhere inbetween tblSingles and tblPublisherShares?

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

    Re: Missing field data (97)

    Fields don't need to have the same name in order to be joined. If they are the same datatype (or compatible, like AutoNumber and Long), then you can join on them. So it is entirely possible to have a SongID in one table and a SingleID in another and join on those fields. That doesn't mean that they necessarily represent the same thing, but if they do then there's nothing wrong with the join.
    Charlotte

  10. #10
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Missing field data (97)

    SongID and SingleID just happen to have different names - SingleID is an AutoNumber and SongID a number field. Yes there is lots of data in the CD_CatalogueNumber field, but nothing displays in the query results.

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Missing field data (97)

    Just grasping at straws here: do you see data in the CD_CatalogueNumber field if you increase the row height and/or column width in the query?

    If not, could you post a stripped down version of your database? I hesitate to ask this, but this seems to be a bizarre problem.
    <UL><LI>Create a new, blank database.
    <LI>Import tblSingles and tblPublisherShares and the query into the new database.
    <LI>Delete almost all records from both tables; leave only just enough to demonstrate the problem in the query.
    <LI>Compact the database (Tools/Database Utilities/Compact Database)
    <LI>Create a .zip file with the database; it should be less than 100 KB.
    <LI>Attach the zip file to a reply. Warning: if you preview your reply, you will have to re-enter the attachment.[/list]

  12. #12
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Leuven, Vlaanderen, Belgium
    Posts
    322
    Thanks
    9
    Thanked 0 Times in 0 Posts

    Re: Missing field data (97)

    OK, <img src=/S/humpty.gif border=0 alt=humpty width=15 height=15> I agree, of course, with Charlotte. No-one is forbidden to use identical ID's (or...) for different entities, or different name for the same 'field' in different tables, or even in the same table. I just don't consider it such a common practice that it doesn't raise any questions when there's a join presented between two fields which by their name, if not by content, might as well indicate something rather different.

    Further I agree totally with HansV asking for a small 'postable' extraction of your database, so that we might examine it 'live'...

    ps Just one more try: don't you have a combo box behind the 'empty' field referring to a wrong data source? Then, it might find no match between the actual stored data and the combo box entry data, resulting in a blanc white field...

  13. #13
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Missing field data (97)

    I agree that the field names should be the same in both tables, but haven't had time to revise the database (and all the attached queries etc.), although I still doubt that this would cause a problem. In response to the previous reply, I have increased the width of the field with no data.. nothing is hidden away. The CD_CatalogueNumber field is just a text field.. it's not determined by a Lookup.

    I'll get a cut-down version of the database, but I can't do this straight-away. I'll post it when ready. Thanks everyone for your continued assistance. Andy.

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

    Re: Missing field data (97)

    Don't worry about it Andrew. It is quite common for developers to use different key names in different tables to avoid the need to alias fields when both are present in a query. The only "problem" that is causes is that Access doesn't automatically create joins when you drag both tables onto the query grid, which in my mind isn't a problem at all. I generally do NOT use the same name for keys in different tables, although in my case I tend to keep a root name and give it a prefix that indicates the table. In some versions of design tools it used to be impossible to create fields with the same name in different tables because it was a violation of some stringent design concepts that required unique field names in all tables.
    Charlotte

  15. #15
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Leuven, Vlaanderen, Belgium
    Posts
    322
    Thanks
    9
    Thanked 0 Times in 0 Posts

    Re: Missing field data (97)

    <img src=/S/crybaby.gif border=0 alt=crybaby width=15 height=15> <img src=/S/crybaby.gif border=0 alt=crybaby width=15 height=15> <img src=/S/crybaby.gif border=0 alt=crybaby width=15 height=15> Communication problem. <img src=/S/please.gif border=0 alt=please width=31 height=23> I saw the different key names. I wondered what might be the cause. First option: just different names, but same content. OK. Won't influence anything. No questions to be asked. Second option: there might be a relation table missing (= e.g. = song_ID+singleID+....) which could explain the different names and maybe also the malfunctioning of the query. Small chance, but you never know. That's why I asked. Nothing more, nothing less.
    As this issue about using which field names almost starts looking like a much-a-do-about-nothing-thread in this thread, and I don't want to look like an idiot, I just want to 'round this up'. Sorry for making myself not clear enough. OK? Thanks.

Posting Permissions

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