Microsoft Access data problems

Microsoft Access data problems

Didn't find your answer?

I have one table that includes order information to include account codes. I have linked to another table that contains the account names.

I have an order that has two lines - the account code on one of the lines does not exist in the description table and so does not appear when I run my query, but does when I do not link to the description table.

I have tried various functions to show a string such as "name not found" if an account code is not in the description table, but have still not managed to get it to work. Even if I just link the tables but do not extract the description field, the line still does not appear. Is there a way to get round this rather than Access just ignoring this field. I came across this problem several years ago but can't remember if we managed a solution or not.

Many thanks
StevenHammond

Replies (7)

Please login or register to join the discussion.

aw_logo_2019
By Accounting WEB
13th Dec 2002 16:20

Account Code not found
I don't think that the other respondents to your question have quite grasped your problem (or perhaps I haven't!). As I understand you, you want to preserve and implicitly show the (presumably) many to one relational linkage between the tables except in the special case when there is no record in the Description Table, when you want to see a warning message. You presumably are trying to do this because the missing description indicates bad data that you need to fix. As you have found, this problem is distinctly non-trivial. I don't think that I can solve it in 2000 characters, but can think of a few approaches if you want to correspond by email.

Thanks (0)
avatar
By neileg
12th Dec 2002 12:24

Nature of the link
The default link in Access is a one to one. The record will only be returned if the data exists in both fields. In the query design view, right click the line that links the tables, and change the relationship. I presume you want to show all of the account codes and only the account names where these match. You'll see what I mean when you get the relationship dialog box on screen.

Good luck

Thanks (0)
avatar
By neileg
12th Dec 2002 12:26

PS
Paul was typing his answer as I was typing mine! You can either right click the join or double click it as you prefer.

Thanks (0)
avatar
By neileg
16th Dec 2002 08:55

Perhaps, Peter
I had not seen the interpretation you have come up with, but you may well be right. If I was going to address the problem you point out, I would look to expand the account name table so that there was a text description for every code, but the text may be 'Description Not Found'.

I would tackle this by creating a meta table with the account codes and descriptions and running an update query to fill the gaps.

Thanks (0)
avatar
By neileg
18th Dec 2002 10:53

Thank you, James
I've used the Nz function a lot, but always in arithmetic to force the value to zero. I have completely overlooked the fact that you can return any value you care to specify from a null entry. Thanks for pointing this out.

Thanks (0)
avatar
By KetcZ
18th Dec 2002 16:43

James Cullingham has got it right
Thank you to James Cullingham for introducing the Nz() function which is much clearer and more brief than using the iif() function.

I have created an mdb file (23Kb Zipped) which includes a query using the Nz() function with sample data in two tables.

I also have also included a possibly useful form with an example of what could be done with a NotInList Event to detect the error at Order entry time with an option for updating the Description table.

You can download it from:
http://www/members.aol.com/zctekfree/accountingweb/accountcodes.zip

Thanks (0)
aw_logo_2019
By Accounting WEB
17th Dec 2002 20:35

Access Data
it sounds like you have a issue with the query

Make the join between tables show all on the many side of the join, if you send me the mdb i will look into for you

Thanks (0)