Three Fields Searched in One Query

unfortuately I don't have a version of Access here at work that can open an accdb file, still on 2003 :mad:
bceo, try adding an autonumber field and make it the primary key, then try again

David
 
I did not have an primary key in the TBLLicNo table. I have set up one as an ID Auto Number field. I have the exact type of primary key in the db that has the data in it that I wish to append from. It had no effect on the results.

What sort of re-structuring do I need to do to Lic # field?
 
Now that BVAInet has uploaded the mbd version, I can see the problem(s).
First your design of TBLLicNo is not quite right, you should have these fields
add new field LicNoPK, select 'autonumber' as the datatype and set it as the primary key.
Delete the 2 name fields and add a new field, call it StaffID and select 'number' as the datatype
Keep the Lic#1 field but change the name to LicNumber, select 'text' as the datatype
delete Lic#2 & Lic#3 as these are NOT needed

Lastly change your append query so that the sql reads

INSERT INTO TBLLicNo ( StaffID, LicNumber )
SELECT TBLLicenseNumber.ID, TBLLicenseNumber.[Lic #1]
FROM TBLLicenseNumber;

so the ID field is appended to StaffID and Lic#1 is appended to LicNumber
run this as to create records for all staff the have a value in Lic#1, then run it again but select Lic#2 field to be appended to LicNumber
then a third time to create records for Lic#3

Notice this new table will now join to the original table using
TBLLicNo.StaffID ---> TBLLicenseNumber.ID

Hope all this makes sense

David
 
Thank you for the information. I have a question. In the main table I have FirstName and LastName fields. If, as you suggest, I delete the two fields in the new table and add the StaffID field, how will it find the information, the field names do not match. Can I use the lastname field instead of StaffID?
 
bceo, one of the fundamentals in relational database design is to try and make where possible all PK's in tables as numbers because with number keys, indexing runs much faster, anyway that aside
these 2 tables will now join on
TBLLicNo.StaffID ---> TBLLicenseNumber.ID

so if you open a new query and join the StaffID field to the ID field, you will be able to return all vehicle license numbers for each staff and the staff names can be got by adding the first/last name fields to your query
BTW now that you have a separate table to store vehicle license numbers, you might want to change the name of your TBLLicenseNumber table to TBLstaff and add other fields to store other staff details, just a thought to giving the table a more meaningful name

David
 
Obviously this is harder than I thought, it most likely because I am not a programmer, but I am still having a problem.
I made the changes in the db layout as you mentioned in the previous reply.
I assume that the StaffID field will now hold the staff names that were in the first and last name fields in the main DB? Since this is only a sample db it was hard to move the data.
The query still does not move the data to the new LicNumber table.
the SQL for the new append query looks like this

INSERT INTO TblLicNo ( StaffID, LicNumber1 )
SELECT TBLLicenseNumber.StaffID, TBLLicenseNumber.LicNumber1
FROM TBLLicenseNumber;

As you can see the second line is not the same as yours
 
sorry bceo you're not understanding the first paragraph of my previous reply. The new staffID will hold the ID number from the old table TBLLicenseNumber because ID is the primary key of the staff table and that is all you need to link back to that table.
Each new record would look like this:
Code:
[U]LicNoPK[/U]   [U]StaffID[/U]   [U]LicNumber[/U]
1         2         WRI 812
2         2         NVB 669
3         2         CDM 567
These 3 records would show all the vehicles for John Smith in your previous set up, notice the StaffID is number 2 which is foreign key to your previous table

I think you would benefit greatly on reading up on Relational Database design and Normalisation

David
 
Thank you for your comments and it is obvious that I need more research in these areas. Until then, I have re-built the sample database and have attached it. It still does not want to work, but I have taken your many suggestions into account during the rebuild.
Each of the dbs has an ID auto number primary key field.
Using the Relationship button I have connected the two fields.
The query was setup using the db that contains the data and using the append button I selected the db that is empty.
The data still does not move.
Once again thanks for your help and patience during these days trying to assist me.

View attachment Sample Licence Number DB.mdb
 

Attachments

Create a new Column in the Query say: Search, joining all the three columns of information with a space: Search:[Column1] & " " & [Column2] & " " & [Column3].

You can use the Like "*" & [EnterTextToSearch] & "*" expression as criteria, under the Search column, to enter the search text directly and find the record(s) that matches anywhere within the new Column.
 
Last edited:
Thank you very much your suggestion works perfectly. :)
 

Users who are viewing this thread

Back
Top Bottom