query on a query not working properly

[lmcc007]

qryTotals -- I am trying to display the total number of fields, so I built a query using other queries, but it is not totalling correctly.

I want:

1. Total interviews per company
2. Total meetings per company
3. Total training per company
​

along with other information.

Do I join the queries correctly? What am I doing wrong?

Attached is the little db.

 

[boblarson]

You need to also limit by the other field - EventTypeID.

Check out my modifications to your queries (all of them for that totals one and the totals one).

 

[jzwp22]

I assume you want a summary that gives the # of each event type per company (and you don't want to see the details). To do this include the company table and the 3 counting queries in a new query. Make a left join from the company table to each of the queries (joining via the companyID field). This will give you the count for each event type for each company. If there is no count value in a particular event type, you will not have any value (it will be null). Assuming that you probably would want to see a zero, you will need to check for a null using the IsNull() function nested in an IIF() function for each event. I went ahead and included that query in the attached database (query name: qrySummaryByCompany)

 

[lmcc007]

You need to also limit by the other field - EventTypeID.

Check out my modifications to your queries (all of them for that totals one and the totals one).

Hello boblarson,

for some reason I can't open the db now. I saved as Access 2000.

 

[boblarson]

1. It still had Office 2007 references when I opened so I had to set them to mine.

2. Hold the shift key down and then go to the VBA window and to TOOLS > References and uncheck the ones marked MISSING.

3. Then reopen.

 

[lmcc007]

I assume you want a summary that gives the # of each event type per company (and you don't want to see the details). To do this include the company table and the 3 counting queries in a new query. Make a left join from the company table to each of the queries (joining via the companyID field). This will give you the count for each event type for each company. If there is no count value in a particular event type, you will not have any value (it will be null). Assuming that you probably would want to see a zero, you will need to check for a null using the IsNull() function nested in an IIF() function for each event. I went ahead and included that query in the attached database (query name: qrySummaryByCompany)

Thanks jzwp22,

That's what I wanted but I also wanted the EventDate, EventTime, EventType, and EventStatus. So, do I add the Event query or table?

 

[jzwp22]

Bob's solution gives the details you want. If you want to have zeros show up, you can use the IIF(Isnul()) approach I illustrated in my query.

 

[lmcc007]

1. It still had Office 2007 references when I opened so I had to set them to mine.

2. Hold the shift key down and then go to the VBA window and to TOOLS > References and uncheck the ones marked MISSING.

3. Then reopen.

No, I couldn't download it. Meaning, I got Windows cannot open this file. ... use the Web service to find the correct program...

 

[boblarson]

Thanks jzwp22,

That's what I wanted but I also wanted the EventDate, EventTime, EventType, and EventStatus. So, do I add the Event query or table?

You add those in and then you will end up with duplicates showing of the counts, because that then goes back to detail data.

 

[lmcc007]

Bob's solution gives the details you want. If you want to have zeros show up, you can use the IIF(Isnul()) approach I illustrated in my query.

For some reason I can't download Bob's db to see his solution.

 

[boblarson]

No, I couldn't download it. Meaning, I got Windows cannot open this file. ... use the Web service to find the correct program...

Here it is again - zipped.

Next time, just right click on the file and select SAVE TARGET AS... you don't need to left click to download.

 

[lmcc007]

Here it is again - zipped.

Next time, just right click on the file and select SAVE TARGET AS... you don't need to left click to download.

They looked the same.

 

[boblarson]

They looked the same.

What do you mean? Clicking SAVE TARGET AS will bring up a dialog which let's you choose where to save the file.

 

[lmcc007]

What do you mean? Clicking SAVE TARGET AS will bring up a dialog which let's you choose where to save the file.

Okay, I see. So, you have to join CompanyID and EventType from the queries to Event.

 

[lmcc007]

You add those in and then you will end up with duplicates showing of the counts, because that then goes back to detail data.

Well, I just had a thought. Maybe I can put the totals in a separate form and add them as a subform to the form with the Event detail info.

Getting totals this way, is better than using DCount?

 

[lmcc007]

You need to also limit by the other field - EventTypeID.

Check out my modifications to your queries (all of them for that totals one and the totals one).

Okay boblarson,

Reading Access 2007 Inside Out, page 424, it joins different. Going by it's example I should join:

1. join CompanyID from Event table to CompanyID in qryTotalInterviews
2. join CompanyID from qryTotalInterviews to CompanyID in qryTotalTraining
3. join CompanyID from qryTotalTraining to CompanyID in qryTotalMeetings

What do you think?

 

[lmcc007]

I assume you want a summary that gives the # of each event type per company (and you don't want to see the details). To do this include the company table and the 3 counting queries in a new query. Make a left join from the company table to each of the queries (joining via the companyID field). This will give you the count for each event type for each company. If there is no count value in a particular event type, you will not have any value (it will be null). Assuming that you probably would want to see a zero, you will need to check for a null using the IsNull() function nested in an IIF() function for each event. I went ahead and included that query in the attached database (query name: qrySummaryByCompany)

jzwp22, thanks for the suggestion of If Null...

 

[boblarson]

Nope, not going to work properly if you do it that way. If every table has entries for those items and there aren't anything missing then it could. But the way jzwp22 has it is the right way. If you add the extra stuff you will have to live with multiples for each event. If this is going to be used in a report you could use it with the stuff jzwp22 gave you as the source for the main report and then the rest as a subreport on the report. I think...

 

[lmcc007]

Nope, not going to work properly if you do it that way. If every table has entries for those items and there aren't anything missing then it could. But the way jzwp22 has it is the right way. If you add the extra stuff you will have to live with multiples for each event. If this is going to be used in a report you could use it with the stuff jzwp22 gave you as the source for the main report and then the rest as a subreport on the report. I think...

So, you're saying if Interviewing, Meeting, and Training all have entries, it will work--but, if not, it will not work?

Yep, subreport--because basically I want the totals and then a list below.

My biggest issue is understanding the query on query joining. For reason I keep thinking daisy chain linking.