Trouble getting the right result from a query

**JeroenMioch** · 04-03-2018, 02:59 AM

Hey guys,

I have three unbound controls on my form :

Number of keys
Number of locks

Serialnumber

I want to check my database for serialnumbers that match my criteria, e.g. find 3 locks and 4 keys with matching serialnumers !
In the unbound controls (keys and locks) i input the number of matching keys and locks i wanna check, and the serialnumer must reveal what the serialnumber of the first matching set is, if any.

Any idea how i go about this ?
I made a query but it seems to only find one matching criteria (locks OR keys) not both.

**ranman256** · 04-03-2018, 05:05 AM

if you only search on the serial, how many locks/keys does it return?

**ssanfu** · 04-03-2018, 05:39 PM

You didn't describe the table(s) structure(s).
And it would help to see some data and expected results.

Maybe:

If the table names are
tblLocks: fields Locks, SN
tblKeys: fields Keys, SN

qryLockCount: a totals query counting locks, grouped by serial number

Code:

SELECT Count(tblLocks.Locks) AS CountOfLocks, tblLocks.SN
FROM tblLocks
GROUP BY tblLocks.SN;

qryKeysCount: a totals query counting keys, grouped by serial number

Code:

SELECT Count(tblKeys.Keys) AS CountOfKeys, tblKeys.SN
FROM tblKeys
GROUP BY tblKeys.SN;

qryKeysLocks: another query based on the first 2 queries

Code:

SELECT qryKeysCount.CountOfKeys, qryLockCount.CountOfLocks, qryKeysCount.SN
FROM qryKeysCount INNER JOIN qryLockCount ON qryKeysCount.SN = qryLockCount.SN;

Final query:

Code:

SELECT qryKeysLocks.SN, qryKeysLocks.CountOfLocks, qryKeysLocks.CountOfKeys
FROM qryKeysLocks
WHERE (((qryKeysLocks.CountOfLocks)=[Forms]![FormName].[tbLocks]) AND ((qryKeysLocks.CountOfKeys)=[Forms]![FormName].[tbKeys]));

"tbLocks" = the unbound control on form for the number of locks ("tb" = Text box)
Change "FormName" and the unbound text box control names to your names.

**JeroenMioch** · 04-04-2018, 08:44 AM

Hi Ssanfu,

Thanks for taking the time to help.
Yes, i didnt provide enough information i guess.

I have one table (tblSluitsystemen) with the field SerieNummer and field Soort.
The field SerieNummer holds the serial number of the locks or keys.
The field Soort holds the information if its a lock or a key. So it is either a 'Cilinder' (Dutch for lock) or a 'Sleutel' (Dutch for key)

Offcourse, a serialnumber is NOT unique. You might have a lock with a matching key. Or multiple locks where the same key fits.
The whole point of me doing this is because we have a stock with locks and keys, and i want to be able to find specific needs asked withing the buisiness.
So if they ask me for 3 locks with the same serial number, and 4 matching keys, i want to check if they are in the database (and thus in stock).

Hence the unbound controls :

"fldAantalDuplicatenCilinders" <----translated fldNumberDuplicateLocks"
"fldAantalDuplicatenSleutels" <----translated fldNumberDuplicateKeys"

If i fill in the number of locks im looking for in the first field, and the number of keys im lokking for in the second, and press a search button.
I want the third unbound control :

"fldBeschikbaarSerieNummer" <----translated fldAvailableSerialNumber"

to display if there is a match, and if so what is the serial number.
Once i know what the serial number is, i can go to the stock in our basement and look for the specific set.

I hope i explained it well enough. Ive been trying to find the solution myself for days but im getting nowhere, although it doesnt seem all the difficult ..