Retrieving all results in a query that is based off of queries with criteria

[Voltron]

If/When (most likely when) this becomes way to complicated to understand please feel free to ask for clarification. I am unable to put any code or databases on here as of yet due to permission issues at my place of business.

Here's the deal...I'm creating a database for my company. The database will keep track of our clients and the different fee they pay and assets they have along with the usual suspects (address, phone number, etc.) Essentially it is going to have queries that bring in data from 5 tables, but will also have to be based off of other queries. The reason for this is because many of the necessary fields are calculated fields, due in large part to the complicated fees structure and multiple exceptions to payment rules that our clients have.

I have a query called Summary Query with the following fields (abbreviated for privacy reasons):

Client_ID (PK in its table)
Recordkeeper_ID (PK in its table) [this is just an add on that needs to be there for the output, but is not causing any problems]
Participants (a whole number)
Fees

Fees consists of 4 calculated fields that are stored in 4 separate queries. 3 of these queries have criteria that limit the output for their specific query, such as Like "*Loans".

The problem is this...I need to get all of the clients to show up when I put the calculated field together. What is currently happening is that only 1 of the 4 test clients that are loaded into the database right now is showing up. What is showing up is correct (indicating that the calculated field I have that is based off of the 4 other calculated fields in 4 separate queries is correct). This is happening because this is the only client which meets all 3 criterion (1 in 3 of the 4 queries that the Summary Query is based off of.

Is there anyway to get all of the clients to show up in the Summary Query while still using the same 4 queries with calculated fields to create the Fees column in the Summary Query?

I am unable to take off the criteria on the other queries as it would mess with the overall numbers.

I realize that I can do this by having about 5 separate "Summary" queries and compiling them into Excel (which is where the information will eventually end up) and running a VLOOKUP's and Sum formulas to get the finished product (which I know how to automate). However, management really wants it in 1 query so I am trying to exhaust every possible option.

Thank you in advance for any help you give.

Once again please feel free to ask for clarification. I am trying to get management to let me rework all of the names and info in the database so that it is completely fake information, but as of right now I cannot get anything on here.

 

[pbaldy]

Presuming you are joining the queries to a master table or query, it sounds like you need to change the join. With the query in design view, right-click on the join line between the key fields to edit it. You want the appropriate "Include all records from..." option.

Edit: I should add that when there is no corresponding record in a "calculation" query, you'll get a Null. You'll probably want to use the Nz() function to handle them.

 

[Voltron]

You sir are a genius, both for knowing the answer and for being able to follow what must surely be a convoluted initial post.

That worked perfectly. Thank you very much for you help.

 

[pbaldy]

I speak "convoluted" fluently; it's my native tongue.

Happy to help.

 

[Voltron]

I now seem to have another problem with this issue.

FEES is a calculated field. It is pulling in calculated "fee" fields from 5 separate queries. There will be FEES for every single client, but the inclusion of the other types of supporting "fees" (of which FEES is made up) will vary, thus the many queries.

The changing of the Join properties allows me to get this output, but is there a way to have the calculation computer properly when no value is returned in some of the supporting "fees"?

Essentially, how can I get Access to calculate all of the Clients' FEES while taking into consideration all of the other queries?

EDIT: Does this have something to do with null values (one of my mosted cursed enemies)? PBaldy mentioned this before, but I wasn't exactly sure how to implement it or if it was referring to this specific problem.

Also, sorry for the messiness of the data, I tried to get it in here better, but it won't format very nicely.

 

[pbaldy]

More than likely, yes. That's why I mentioned the Nz() function earlier. Instead of

Field1 + Field2

you'd use

Nz(Field1, 0) + Nz(Field2, 0)

 

[Voltron]

More than likely, yes. That's why I mentioned the Nz() function earlier. Instead of

Field1 + Field2

you'd use

Nz(Field1, 0) + Nz(Field2, 0)

Sorry for the confusion over that. I am not exactly sure where to put this code. Do I create a module in the visual basic editor or add it into the SQL or...?

Sorry if this is a stupid question, I've never dealt with a problem quite like this.

 

[pbaldy]

Wherever you are currently doing your calculation. Where is that?

 

[Voltron]

It is currently in the Field: section of the query design.

I wasn't sure if I needed to create VBA for it or not.

 

[Voltron]

FEES: [Fees]+Nz([Brokerage_Fees],0)

Something like this in the Field: section right? This seemed to work, but I'm not exactly sure if this is what I was supposed to do.

EDIT: That seemed to work perfectly. The numbers match up to what I did by hand so everything looks good.

Thanks again for all of your help I really appreciate.

I am now 1 step closer to defeating, once and for all, that dreaded enemy of mine Nz().

 

[pbaldy]

No problemo. Null is your enemy; Nz() is your friend.