Matching records query (1 Viewer)

KGaccess

New member
Local time
Today, 15:54
Joined
Sep 15, 2020
Messages
6
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

I’m here to help
Staff member
Local time
Today, 08:54
Joined
Oct 29, 2018
Messages
21,358
Hi. When you say "whole database," how many tables are we talking about where this same serial number might be stored?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:54
Joined
May 7, 2009
Messages
19,169
select field1, field2, field3, field4, field5 from table
where Instr("|" & field1 & "|" & field2 & "|" & field3 & "|" & field4 & "|" & field5 & "|", "|" & Forms!FormName!textbox & "|") > 0
 

KGaccess

New member
Local time
Today, 15:54
Joined
Sep 15, 2020
Messages
6
Its just one database
I am a bit new to this so am assuming from arnelgp response that
Code:
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

The Mailman - AWF VIP
Local time
Today, 16:54
Joined
Aug 11, 2003
Messages
11,696
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

New member
Local time
Today, 15:54
Joined
Sep 15, 2020
Messages
6
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

..forever waiting... waiting for jellybean!
Local time
Today, 23:54
Joined
May 7, 2009
Messages
19,169
here is a sample.
yes, it's a query.
 

Attachments

  • sampleAgain.zip
    46.8 KB · Views: 256

Minty

AWF VIP
Local time
Today, 15:54
Joined
Jul 26, 2013
Messages
10,354
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

I’m here to help
Staff member
Local time
Today, 08:54
Joined
Oct 29, 2018
Messages
21,358
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

The Mailman - AWF VIP
Local time
Today, 16:54
Joined
Aug 11, 2003
Messages
11,696
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.
 

namliam

The Mailman - AWF VIP
Local time
Today, 16:54
Joined
Aug 11, 2003
Messages
11,696
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
Code:
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]));
 

Users who are viewing this thread

Top Bottom