Matching records query

[KGaccess]

I have a database with 5 fields, each field contains serial number records. The same serial number can appear in one or more fields (can be duplicated)

I need to make a query that if I enter a single serial number (ideally from a form), the query searches the whole database and reports the only record rows where the same serial numbers are present.

Any help please?

 

[theDBguy]

Hi. When you say "whole database," how many tables are we talking about where this same serial number might be stored?

 

[arnelgp]

select field1, field2, field3, field4, field5 from table
where Instr("|" & field1 & "|" & field2 & "|" & field3 & "|" & field4 & "|" & field5 & "|", "|" & Forms!FormName!textbox & "|") > 0

 

[KGaccess]

Its just one database
I am a bit new to this so am assuming from arnelgp response that

where Instr("|" & field1 & "|" & field2 & "|" & field3 & "|" & field4 & "|" & field5 & "|", "|" & Forms!FormName!textbox & "|") > 0

is the criteria in the query? Does it go in each criteria for all 5 fields?

 

[namliam]

select field1, field2, field3, field4, field5 from table
where Instr("|" & field1 & "|" & field2 & "|" & field3 & "|" & field4 & "|" & field5 & "|", "|" & Forms!FormName!textbox & "|") > 0

Really? Sorry to be harsch but that must be the worst way ever to do this.... IMHO offcourse

Do you know how to add criteria to fields ?
Field1 = Forms!FormName!textbox
OR Field2 = Forms!FormName!textbox
OR Field3 = Forms!FormName!textbox
etc

Why would you have a table with 5 field each field containing a serial number, this sounds like a "less than optimal" situation.

 

[KGaccess]

I will try to explain. I have a PCB type. There could be 100 of them so 100 serial numbers of the same PCB.

As a PCB gets put in the next assembly up I want the main assembly to record the serial number of the PCB installed. The complexity comes because throughout the main assemblies life a PCB can be changed upto 5 times, sometime the same PCB will be refitted and sometimes a new one.

I need to see on what main assemblies, PCB serial number XX has been installed and how many times.

So my fields where the serial numbers are stored are;

MainPCBInfo#1 to MainPCBInfo#5

and then i have main assembly number field



My query uses these as the fields and has this criteria in each field column.

InStr("|" & "MainPCBInfo#1" & "|" & "MainPCBInfo#2" & "|" & "MainPCBInfo#3" & "|" & "MainPCBInfo#4" & "|" & "MainPCBInfo#5" & "|","|" & [Forms]![Find HS MAIN PCB]![Serno1] & "|")>"0"

If I run the query (via a form) I want to be able to enter one serial number and have the query report all instances of it by main assembly number.

 

[arnelgp]

here is a sample.
yes, it's a query.

 

[Minty]

You should have a table of PCB's fitted as a child table of your Assemblies, this way you could have as many fitted or refitted as you needed, and searching for duplicates would be simples.

 

[theDBguy]

Its just one database

Yes, we knew that. There was an obvious question about storing the same data in multiple fields, but I was just wondering if there was a non-obvious question about storing the same data in multiple tables as well when you said "whole database."

In any case, looks like you are on your way to get what you want now. Good luck with your project.

 

[namliam]

Using numbered fields in a database is a sure fire way of making it like an excel sheet, which is a bad thing to do in a database.

As I stated earlier and Minty did as well, redesign is in order to create a single table with 2 columns... 1 column holding your PCBInfo.

 

[KGaccess]

here is a sample.
yes, it's a query.

Thanks arnelgp!! works great and I understand it

 

[namliam]

Thanks arnelgp!! works great and I understand it

Nice that it works and nice you understand it, but it stil is less than optimal design and much less than optimal solution.
If you are stuck on this design, this query would work much better

SELECT Table1.[MainPCBInfo#1], Table1.[MainPCBInfo#2], Table1.[MainPCBInfo#3], Table1.[MainPCBInfo#4], Table1.[MainPCBInfo#5]
FROM Table1
WHERE (((Table1.[MainPCBInfo#1])=[Forms]![Form1]![text0])) 
OR (((Table1.[MainPCBInfo#2])=[Forms]![Form1]![text0])) 
OR (((Table1.[MainPCBInfo#3])=[Forms]![Form1]![text0])) 
OR (((Table1.[MainPCBInfo#4])=[Forms]![Form1]![text0])) 
OR (((Table1.[MainPCBInfo#5])=[Forms]![Form1]![text0]));