Missing field data (97)

Topic

New Reply

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

Reply | Quote

Replies

Hi Andy,

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

Reply | Quote

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

Reply | Quote

Andy,

If this is related to the same question you asked in Post 246004, 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.

Reply | Quote

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?!

Reply | Quote

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

Pat

Reply | Quote

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).

Reply | Quote

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 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?

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

• Create a new, blank database.
• Import tblSingles and tblPublisherShares and the query into the new database.
• Delete almost all records from both tables; leave only just enough to demonstrate the problem in the query.
• Compact the database (Tools/Database Utilities/Compact Database)
• Create a .zip file with the database; it should be less than 100 KB.
• Attach the zip file to a reply. Warning: if you preview your reply, you will have to re-enter the attachment.
  [/list]

Reply | Quote

OK, 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…

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

Communication problem. 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.

Reply | Quote