Query multiple tables and Count rows based on criteria

[MattioMatt]

I'm trying to count rows that equal a value from 3 tables linked to another table and I'm having trouble.

I have the following:

tblAssets
AssetID | IP Address | Asset Name

tblVuln_A (Relationship 1-M from IP in Assets Table)
VulnID | IP Address | VulnTitle | Severity (Number 4 or 5)

tblVuln_B (Relationship 1-M from IP in Assets Table)
VulnID | IP Address | VulnTitle | Severity (Number 4 or 5)

tblVuln_C (Relationship 1-M from IP in Assets Table)
VulnID | IP Address | VulnTitle | Severity (Number 4 or 5)

I have a continuous form at the moment that is based on a union query for listing all entries from any of the 3 tables. I'd like to put a textbox on the form to count the number of Sev5's then another for counting the total number of Sev4's against the asset the form has been opened for.

I've created a pair of queries for each of the Vuln tables to pull records where Sev 4 or 5 then crearted a pair of union all queries to pull it in from all 3 tables.

Example:

Select * from qryVuln_ASev5
UNION ALL Select * from qryVuln_BSev5
UNION ALL Select * from qryVuln_CSev5

The problem I have is trying to count up the rows from the union queries and add it to a bound continuous form (form is bound to a union all to list all the vulnerability records relating to an asset).
I've tried using an expression but it doesn't seem to be working as it is giving me incorrect results.

Any help most appreciated.

 

[plog]

You need to fix your tables. Read up on normalization (https://en.wikipedia.org/wiki/Database_normalization). That's the process to properly structure your tables. Here's the 2 big things I see:

1. Same structured tables. When you have tables with the exact same structure (_A, _B, _C), you are effectively storing a value in the table name. Instead, all that data goes into the same table with a new field to distinguish if the record is for A, B or C.

2. Numerated field names. Similar to #1, when you have fields that are named with numbers (Sev1, Sev2, Sev3, etc.) you are storing a value that belongs in a field (the number suffix) in the field name. to fix this, it requires a whole new table. And in that new table, instead of 5 fields for your values, you make 5 records for that (one per value).

You make both those changes and the query you want becomes a trivial aggregate query (https://support.office.com/en-us/ar...c4b-b154-8c8dbbe41c8a?ui=en-US&rs=en-US&ad=US). Lastly, its odd that you are using [IP Address] from tblAssets as a foreign key in other tables. Usually you use the ID field of a table (AssetID) in outside tables to link them. Why are you using IP and not AssetID?

 

[MattioMatt]

Hi plog,

1. The tables don't have the same columns, hence why they are separate. They have between 10-20 columns different, I didn't want to list them all without it become to long. I apologise I should have been clearer on that.

2. Again the column name in the Vuln tables is just called 'Severity' The brackets '(Number 4 or 5)' was for the purposes of this post to show it will either be a 4 or a 5 as a value, again I wasn't clear and I apologise.

 

[plog]

They have between 10-20 columns different,

Now, I'm really confused. Does the UNION query you posted produce results?

 

[plog]

Strike that--my last question, not the part were I am really confused--that still holds. I see that your UNION is based on 3 queries.

What I am confused on now is where your issue lies. Is it in a query? Or is this issue in a form? Do you have a query that produces the right results?

 

[MattioMatt]

Hi Plog,

Yeah the query pulls the correct results, 2 rows as I have put one row of Sev 5 into two of the tables.

The problem is trying to get these row as a count into a textbox on a bound form (the form is not bound to this query but another one) but matching the count to the asset ID on the form.

So I would like to have a textbox on the form laballed '# of Sev 5:' then it pulls back the count from the union query in relation to the asset ID of the bound form.

 

[plog]

You would use a DLookup from that text box, it would lookup the value from the query.

 

[MattioMatt]

Forgive me, I thought DLookup was to look up a value?

The union query doesn't pull a total number of rows. It lists all rows where the Severity matches '5' for example:

An asset can have many vulnerabilities each scored either a 4 or 5.

So the union query is pulling back 2 rows for two different vulnerabilities against an asset.

So it pulls back (example)

AssetID | IP | Asset Name | VulnTitle | Severity
1 10.10.10.1 QA-DAA Java Out of Date 5
1 10.10.10.1 QA-DAA OS Out of Date 5

I have have form which displays the list of vulnerabilities linked to an asset, I'd like to add a text box to that form that would show the total rows, so in this example it would be 2, so the total count linked to the asset.

 

[plog]

Correct about DLookup. Instead you will want DCount(https://www.techonthenet.com/access/functions/domain/dcount.php) it counts the number of records in a query. You can even pass it criteria to limit it to certain values.

 

[MattioMatt]

Thanks Plog!

Got it working with that link used the following as the control source on the form - thanks so much for bearing with me.

=DCount("*","qryUnTestSev","[AssetID] = " & [txtAssetID])

 

[MattioMatt]

How many criteria can you have with DCount?

I'm now trying to do the following but not having much luck.

In summary I'd like to count a row if the severity is 5 and the ticket status is not equal to resolved whilst matching it to the AssetID.

I've tried the following but it throws back an error.

=DCount("*","qryUnTestSev","[Severity] = 5 AND [TicketType] <>'Resolved' AND [AssetID] = " & txtAssetID)

 

[plog]

Thats how you do it. What is the error exactly, your syntax looks correct. The only thing I can think of is that Severity is actually a text, in which case you need to put 5 in single quotes.

 

[MattioMatt]

'#Error' which flashes on the form, as though it was on a loop.

I've checked the data type for 'Severity' and it is correctly set as a number.

 

[plog]

Did it work with just 1 criterion in the DCount? If so, I would revert to that and then add 1 more criterion, see if that works, then that means the final criterion is the culprit.

Basically, identify which part of the DCount is hanging things up.

 

[MattioMatt]

Got this working with putting the text part at the front and the following with the numeric.

=DCount("*","qryUnionVulnAssets","[TicketState] <>'Resolved' AND [Severity] = 5 AND [AssetID] = " & [txtAssetID])