Three Fields Searched in One Query

[bceo]

I have a database that records information on the staff working in the building (over 400). This information includes the licence number of any vehicles (up to three) they might be parking in the parking lot. I have created three fields in the table to record the licence numbers of the vehicles.

My problem is that to search the table for a licence number that was recorded, unknown to me, in the third field, I have run the search three times, once for each field.

Is there any way I can setup a query that will check all three fields in one search request.
Alternately is there a way to set up the field so that it contains all three Licences numbers in such a way that a single query will find any one of them when a search is run for a single Licence number.

 

[ConnorGiles]

Two Questions from me Bceo

Will you know the License Numbers?

and

What are the field names

 

[ConnorGiles]

I have made a test database with a Parameter query that may solve your problem from the information you have given me

(A Parameter Query is a query that will prompt you for information so if you would only wish to search for 1 License plate number since all are individual - presuming they are all in one record it should find it out for you)

Pay close attention to the Parameters as you will need those in this given query

The query will ask you for the 3 License Plate Numbers and paired with your table this will show all of their information.

The picture i also linked should show you what i have done

 

[DavidAtWork]

bceo, another approach might be to further normalise your data structure as the relationship of Staff to vehicles is 1 to many, so if you had the staff vehicles stored in a separate table, your query would be much simpler and quicker, to run

David

 

[vbaInet]

bceo, another approach might be to further normalise your data structure as the relationship of Staff to vehicles is 1 to many, so if you had the staff vehicles stored in a separate table, your query would be much simpler and quicker, to run

David

I second that!

You can then either create 3 records per employee or manage the max number of registrations an employee can have in code.

 

[bceo]

ConnorGiles

I all ready have a query set up to search each Licence Number field, but if the number is in the 3rd field, I would have run the query 3 times. I only want to run the query once.
The fields are TblLic #1, TblLic#2 and TblLic#3

DavidAtWork
If I understand your suggestion, you want me to create a new table for the licence numbers and using the relationship system to connect all the Licence number fields to the Name field or do you want me to connect the licence table to the Name Field.

Thanks in advance for all your help

 

[ConnorGiles]

Could you please Link your Database if possible (Take note Zip it to reduce file size) or a sample database with your query inside - Probably best to just put your query inside a new database via import so i can play around with it

 

[ConnorGiles]

And you say "The Number" Would there not be numbers for each field or am i incorrect i n thinking there are 3 fields for One for each car owned (Such as License Plate 1, License Plate 2, License Plate 3)

 

[DavidAtWork]

bceo, yes use a separate table which will have an autoID (autonumber), staffID (same as your staff table), regNo (text) plus any other fields you deem appropriate such as a date entered, an expired field (to indicate if the vehicle is current Yes/No)etc

You can create the records from your existing data by running an append query from your existing data, run it 3 times to pick up entries in fields TblLic #1, TblLic#2 and TblLic#3

There is another benefit in that you don't have to limit the number of vehicles per employee but you can also control the number as well and it would be much easier to count the number of current vehicles per employee etc

David

 

[bceo]

I assume the regNo (text) is the license number. If so does that mean I have to entered each licence number as a separate entry into this new table?

 

[bceo]

IfView attachment Sample Licence Number DB.zip this helps I have attached a simple database with sample data and the queries I am now using.

 

[vbaInet]

Adding to what David advised, StaffID and RegNo (car registration/plate number) could be your Primary Key (or Composite keys), i.e. if employees could use the same car. Otherwise RegNo as a Primary Key would be sufficient if you only want that a car to be entered once. The circumstance would dictate which field(s) to use as the Primary Key. The Autonumber field would act as a counter/control field.

 

[DavidAtWork]

"I assume the regNo (text) is the license number. If so does that mean I have to entered each licence number as a separate entry into this new table? "
bceo, yes regNo was meant to represent the license number. You should be able to populate this new table from your existing staff table. Do you know how to run an append query? As I said before, this will create all the vehicle records by picking up the values from your staff table, staffID and TblLic #1, then run it again using staffID and TblLic #2 and a third time using staffID and TblLic #3

David

 

[bceo]

I created a new table with the Names and License Numbers fields. (TbleLicNo)
I created a query based on the TblLicenseNumber table using only the Name fields and Lic #1.
I click the Append button and when ask where to append to I put in the new table (TblLicNo) and click on Run.
I then opened the TblLicNo table and there was no data in the table.
I have attached the sample database I tested your suggestion on, in hopes that you can advised me where I may have gone wrong.
As always I appreciated the time and effort you are putting into solving my problem.

View attachment Sample Licence Number DB2.zip

 

[DavidAtWork]

bceo, what is the name of your original table, is it TblLicenseNumber?
If so, make sure on the criteria line for field [Lic #1], you specify Is Not Null

David

 

[bceo]

Yes TblLicenseNumber is the original table with the license numbers in it.
I have added Is No Null to the criteria line under the Lic #1 field as you suggest in your last post and click the RUN icon.
It still does not want to work.

 

[DavidAtWork]

Have you run an append query before successfully, maybe you should check on how an append query works. If your table TblLicenseNumber has values in the Lic #1 field and the staffID field, then it should create records in the new table.

David

 

[bceo]

I have taken your suggestion and have read up on Append Query and I do not see my problem.
When I bring up my query window and selected datasheet view I see the 2 entries in my sample DB, which tells me I am doing something right. But when I check the new table there is no entries in it even though I have clicked the "Run" icon. If this helps here is the SQL for the query I have set up and I do not see any thing out of place (though I am not a programmer).

INSERT INTO TBLLicNo ( [Last Name], [First name], [Lic #1] )
SELECT TBLLicenseNumber.[Last Name], TBLLicenseNumber.[First name], TBLLicenseNumber.[Lic #1]
FROM TBLLicenseNumber
WHERE (((TBLLicenseNumber.[Lic #1]) Is Not Null));

TBLLicenseNumber is the original file with the Lic # and names in it
TBLLicNo is the new table I want to populate with the names and Lic #

Thanks again for our patience.

 

[DavidAtWork]

your query looks correct. Does your table TBLLicNo have a primary key defined? If so which field is it and what data type is it?
Also try switching the query to a 'SELECT' query and use the View to see how many records are returned.

David

 

[vbaInet]

[Lic #1]

The field hasn't yet been re-structured properly. You might want to have a look at the db bceo posted earlier David.