General discussion

Locked

Nz Function in Access or an easier way?

By Twila.Mitchell ·
I am trying to create a report that will return a companies main zip code plus the zip codes of any additional locations (if any). The report is actually a letter about a specific client. Basically it says, Client "A" is now sold and needs pertinent information regarding our products in the following zip codes:

I have a table of Client information (name, address, city, state, zip, etc.)

I have a table of extended client information (ExtendedID, ClientID (as a foreign key), Owner's name, Description of Operation, and a cell for whether this client is currently quoted, sold, or declined)

I have a table for additional location addresses. (LocationsID, ClientID(as a foreign key), Address, City, State, Zip) Some clients have additional locations but of course most do not.

I have related the tables together with autonumber fields. (ie: ClientID, ExtendedID, LocationsID)

My relationships seem to have pulled things together nicely except I don't understand how to create a query to support this report that is needed.

I ran a query that collects information from these tables, (client name, clientzip, quotestatus "criteria = "sold", locationzip.

This works fine so long as a client is sold AND has additional locations, but for clients who are sold and have only one location, the query is excluding them. I read something about using an Nz Function but I am not familiar with VB and barely understand macros.

Any help for how to get around this problem would be appreciated.

This conversation is currently closed to new comments.

4 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

by george In reply to Nz Function in Access or ...

I would look at your join properties to the table for additional location addresses. Make sure that your using all records from the other joined table and only records from that additional location address table that match the joined field. It sounds like it's the other way around.

Collapse -

by Konza In reply to Nz Function in Access or ...

When I've experienced this in the past, it had to do with the type of relationship I'd created between tables. Check to make sure your joins permit all records to be seen, not just the ones where data is common in all three tables.

To do this, open your Relationships area (found on the Tools menu). Right click the line between tables and select edit relationship. In the pop up box, select Join Type to see the relationship you currently have defined. If it is set to Join Type 1, you will only return records that are common to both tables. Change it so that the include all records primary table is the larger of the two tables. Continue to check any other relationships you have set to make sure you are getting the join you want.

Collapse -

by Twila.Mitchell In reply to

THANK YOU! Yes, that was the problem and it cleared it right up. Now that I know the answer, it seems absolutely apparent. Thanks again!

Collapse -

by Twila.Mitchell In reply to Nz Function in Access or ...

This question was closed by the author

Back to Software Forum
4 total posts (Page 1 of 1)  

Related Discussions

Related Forums