Query on more than one record that have related data

[trishcollins]

I have a table of data I run a query on. The table has a location code and a account code. A location code can have one or multiple account code records.

I am trying to write a query that does the following:

Filters ONLY on location with one specific account code + at least one other account code of my choosing. I have created a table which is a subset of the Account Codes I want to see in the report.

In the example below, the location must at least have the account code "NPSN" in addition to at least one or more of the other account codes in the account code table. The bold is the base account code. If there is no "NPSN" account code, I don't want to see ANY of the other account code records for that location.

Location ID Account Code
123456 NPSN
123456 Radio
123456 PTSS

In the query, I perform the following to limit the account codes:

=DLookUp("[Account_Code]","NSB Dynadocs NetCon Account Codes","[Account_Code] = '" & [Account Code] & "'")

That at least limits the account codes to only those I want in the report (there are many more I don't want to see). But have yet to figure out how to limit the list so that locations on the report must all have the NPSN account code and one other account code before being included.

I have a feeling this might be something to do with counting records for each location, and then seeing if there is more than one account code and at least one of the account codes is NSPN, but I am not able to figure out the syntax. Any suggestions?

 

[jdraw]

Could be. Can you post a jpg of your tables and relationships?

 

[trishcollins]

I have attached a jpeg of the query that gives me the combination of unique Location ID and Account Codes. Unique values is set to "Yes". And the "Account Code" is a DLookup on a short list of account codes I use, rather than all of them.

When I run the query, it looks like this:

CON1234 NPSN
CON1234 NSP
CON1234 RADIO
CON9876 NPSN
CON4567 RADIO
CON4567 VOIP

Here is the SQL statement for the above query:

SELECT DISTINCT [Dynadocs Inventory].[RCMP Location ID], [Dynadocs Inventory].[Account Code]
FROM [Dynadocs Inventory]
WHERE ((([Dynadocs Inventory].[Account Code])=DLookUp("[Account_Code]","NSB Dynadocs NetCon Account Codes","[Account_Code] = '" & [Account Code] & "'")))
ORDER BY [Dynadocs Inventory].[RCMP Location ID], [Dynadocs Inventory].[Account Code];

I would like to be able to do a count of the Location ID (CON9999) and filter on ONLY those records that meet the following criteria:

[RCMP Location ID] dcount > 1 AND at one of the [Account Code]s for that [RCMP Location ID] must = "NPSN"

The query results then should look like this as only CON1234 met the criteria:

CON1234 NPSN
CON1234 NSP
CON1234 RADIO

Any suggestions?

 

[trishcollins]

Starting to narrow it down, but looking for the correct syntax for the crosstab query for the following:

This first expression counts the number of records in another query [NSB Dynadocs CON Number and Account Code] with a particular [RCMP Location ID]

Total_Distinct:=DCount("*","NSB Dynadocs CON Number and Account Code","[RCMP Location ID] = '" & [RCMP Location ID] & "'")

The second expression counts the number of records that have the same [RCMP Location ID] AND where the [Account Code] = "NPSN". Not sure what that looks like in terms of sytax, think it's wrong:

Total_NPSN:=DCount("*","NSB Dynadocs CON Number and Account Code","[Account Code] = '"NPSN"'" & “ AND [RCMP Location ID] = “ [RMCP Location Number])

Finally, I decide if the record should be included in the query with this statement:

Include_In_Report:=iif(Total_Distinct > 1 AND Total_NPSN=1,”Yes”,”No”)

Can someone validate the syntax for me?

Thanks...Trish

 

[trishcollins]

Regardless of whether or not the syntax is correct, the first code statement above (the only one I have tried) seems to take hours to process and has yet to complete -- I have had to crash Access to get it to stop. I suspect the performance is negatively impacted by both the dcount being done on another query, which subsequently does a dlookup to another table.

I need a more efficient way to do this, so that when my report loads, I only see those specific sites.

Is there any VBA code I could write to put in the Event On Load property of my report, that would allow me to filter on the query that I have already run?

The base query is [NSB Dynadocs for NetCon], the other code in my previous post contains the rest of the info.

I will assume that I may have to convert the existing SQL query (below)to VBA, in order for it all to work:

SELECT DISTINCTROW [Dynadocs Inventory].[RCMP Division], [Dynadocs Inventory].Service, [Dynadocs Inventory].Contract, [Dynadocs Inventory].[RCMP Location ID], [Dynadocs Inventory].[Account Code], IIf(IsNull([End date]),"Active","Inactive") AS Status, DLookUp("[DateUpdate]","NSB Table Names","[Name] = 'Dynadocs Inventory'") AS LastUpdated, *
FROM [Dynadocs Inventory]
WHERE ((([Dynadocs Inventory].[RCMP Division]) Like [Forms]![NSB Dynadocs Inventory Report Query Form]![Division]) AND (([Dynadocs Inventory].Service) Like [Forms]![NSB Dynadocs Inventory Report Query Form]![Type]) AND (([Dynadocs Inventory].Contract) Like [Forms]![NSB Dynadocs Inventory Report Query Form]![Contract]) AND (([Dynadocs Inventory].[RCMP Location ID]) Like [Forms]![NSB Dynadocs Inventory Report Query Form]![CON]) AND (([Dynadocs Inventory].[Account Code])=DLookUp("[Account_Code]","NSB Dynadocs NetCon Account Codes","[Account_Code] = '" & [Account Code] & "'")) AND ((IIf(IsNull([End date]),"Active","Inactive"))="Active"));

Trish

 

[pbaldy]

Domain aggregate functions in queries are notorious for performance problems. Can you post a sample db with enough data in this table to provide a meaningful test? I have a thought, but it would be easiest to test it out than try to describe it.

 

[trishcollins]

Let me put something together and post later today. Thanks!

 

[trishcollins]

I wasn't able to upload my zipped file (being blocked at my end), and my development db is too large to attach. I have attached a couple of jpegs instead (I have removed all sensitive info, such as addresses and costs).

The inital query "NSB Dynadocs Inventory for NetCon" restricts the list to the subset of the table "NSB Dyadocs NetCon Account Codes" by using a DLOOKUP on [Account Code] and works fine. I use this query to run my report. The Query can be seen in the attachment "NSB Dynadocs Inventory for NetCon Query.jpg" and the results of running the query can be seen in the attachment "Query Results for Subset of Location Codes.jpg"

However, it's not exactly what I want. I really want to filter so that only those that are contained in the subet of [Account Code]s with MORE than one unique combination of [RCMP Location ID]&[Account Code] and at least ONE of those [Account Codes]="NPSN" appear on the report.

I started by creating another query called "NSB Dynadocs CON Number and Account Code" and set the Values to unqiue. That gives me a list of unique [Accounts Code]s by [RCMP Location ID]. Again I use the DLOOKUP for the subset of [Account Code] in the table "NSB Dynadocs NetCon Account Codes". See attachment "Query for Unique Location and Account Code Combinations.jpg"

I need a way to count the number of occurances of each [RCMP Location ID] in that new query, then use it to filter on the original query, to basically say "only include those [RCMP Location ID] > 1 AND at least ONE of the [Account Code]s for that [RCMP Location ID]="NPSN" will appear on the report.

You can see in a snapshot of "Report Results for NetCon.jpg" that if I was able to figure out how to filter as I want, that only CON02413 meets the requirements. The others do not meet the criteria, and therefore would not appear on the report.

Any ideas?