Muliple field criteria based on 1 input

[tpeter]

I have a simple query to look at a table for a part number. There are 48 possible fields that the part number could be in. Can I make the criteria based on field 1 = to the other 47 fields? An example would be in Part_Number_1 criteria [Enter Part Number], in fields 2-48 criteria =[Part_Number_1]. I don't have the database in front of me, just curious if this would work. Thanks for your help.

Tim

 

[DCrake]

Don't know how your data is structured but it sounds very denormalised. What senerio means that 1 data item could be in 48 places. How does it get there in the first place?

 

[boblarson]

I have a simple query to look at a table for a part number.

Not with 48 fields to check it isn't a SIMPLE query. It is a very complex one and will be very SLOW to do the search, and as David has said, suspicously not normalized.

Due to the structure, you will have to search 48 fields times the number of records in your database. So, if you have 5000 records it will need to search 240,000 data. So it will be dead dog slow.

 

[tpeter]

Basically there is an area for 48 possible changes to 1 ecn number. They may make one change or 48. They want a lookup (find function) to find a specific part number in the table, it may be in field 1 or 48 and there may be times when a part number has changed mutiple times. So they would like to see all of the enc numbers (product key) that contain that part number.

I am able to use the findrecord macro but this only finds the first record not all of them.That is why I thought a query would make sense but entering the criteria 48 times is not a possiblility. Thanks for your input and any suggestions that you may have are appreciated.

Tim

 

[DCrake]

Long winded but you could use a union query

Select F1 as Prod, "SourceField1" As Location  From Table
Union All
Select F2, "SourceField2" from Table
Union All
Select F3, "SourceField3" from Table
....
....
Union All
Select F48, "SourceField48" from Table

Then do a search on the prod field This will then tell you which fields (SourceField) it was found in.

 

[tpeter]

David,

Thanks for the thought it is greatly appreciated and I will try it out tonight. Thanks again.

Tim

 

[AC5FF]

Amazing.....
I just logged in here to post a very similar question... Not 100% sure this solution will work for me (I'm VERY bad at writing SQL).

My table has over 100 columns and has over 1200 records to date. It is an invoice/work order table so many of the columns are customer/date/etc information. Yes, it's not normalized very well from that standpoint - but this was put together long before I took the job.

As a technician creates the work order/invoice and bills for materials, they can put down material "X" in a number of different fields. I need to be able to pull out each invoice for every time part "X" was sold/used.

I have been just doing a "Find" command while in the table; selecting "Any part of field" ... That works, but then I have to scroll back and forth to check dates/customer number/etc. This method got old REAL quick - that's why I wanted to try a query...

Any other ideas out there that might be able to work out??????

THX!

 

[tpeter]

I am now getting a error on my from statement. I put the first 10 records in as a test but the code will not run. I want all of the ECN numbers that have the part number loaded into the Part_Number_1 criteria. Do I need the from statement since it is only searching 1 table? Here is SQL that I am have:

select Changes.ECN_Number as Prod, "ECN_Number" as location from Table

Union All

select Part_Number_, "Part_Number_1" from Table

Union All

select Part_Number_2, "Part_Number_2" from Table

Union All

select Part_Number_3, "Part_Number_3" from Table

Union All

select Part_Number_4, "Part_Number_4" from Table

Union All

select Part_Number_5, "Part_Number_5" from Table

Union All

select Part_Number_6, "Part_Number_6" from Table

Union All

select Part_Number_7, "Part_Number_7" from Table

Union All

select Part_Number_8, "Part_Number_8" from Table

Union All

select Part_Number_9, "Part_Number_9" from Table

UNION ALL select Part_Number_10, "Part_Number_10" from Table

FROM Changes

WHERE (((Changes.Part_Number_1)=[Enter Part Number]));


Thanks again for all your help.

Tim

 

[vbaInet]

Tim, did you run the query before applying it to your form?

Select * From 

[COLOR=Red][B]([/B][/COLOR]select Changes.ECN_Number As Prod, "ECN_Number" as Location from Table

Union All

select Part_Number_[COLOR=Red][B]1[/B][/COLOR], "Part_Number_1" from Table

Union All

select Part_Number_2, "Part_Number_2" from Table

Union All

select Part_Number_3, "Part_Number_3" from Table

Union All

select Part_Number_4, "Part_Number_4" from Table

Union All

select Part_Number_5, "Part_Number_5" from Table

Union All

select Part_Number_6, "Part_Number_6" from Table

Union All

select Part_Number_7, "Part_Number_7" from Table

Union All

select Part_Number_8, "Part_Number_8" from Table

Union All

select Part_Number_9, "Part_Number_9" from Table

UNION ALL select Part_Number_10, "Part_Number_10" from Table[COLOR=Red][B])[/B][/COLOR] AS Q

WHERE Q.Prod = [Enter Part Number];

By the way you don't have good naming conventions. Words like Table should be avoided.

 

[tpeter]

Thanks for the help. I will give it a try tonight. I am a little confused what you mean by the naming conventions. The table this is named "Changes" and all of the fields are Part_Number_1 ... Do I need to change "from Table" to "From Changes" or do I need to refer to the "ECN_Number" field?

Tim

 

[vbaInet]

They all have to be From Changes not From Table. David wrote that as an example.

And, yes from the way you've name your fields, i.e. Part_Number_1 etc... we suspect the table is not at all normalized.

 

[tpeter]

Ok, thanks again for the clarification. Sorry for being such a pain, never written a manual SQL before. No, the table is not normalized, when this was created with the dynamic field ability should have been created in exel where the lines are already there instead of hidding and unhiding fields. Thanks again.

Tim

 

[tpeter]

Ok, this got me very close. The location is telling me what field it found it in, what I really need to know is what the ecn_Number is.

Prod location
415588 Part_Number_1
415588 Part_Number_1

Once again I am sorry for all the trouble.

Tim

 

[vbaInet]

So you didn't want to look in the Ecn_Number in the first place?

How is the ecn_number related to the part number?

 

[tpeter]

Sorry for the confusion. Here is the goal, put part number in, it then searches and tells me what ecn's (primary key/no duplicates) it finds the part number in. So if I type 415588 into the criteria box I would like it to look in all part number fields(in the Changes table) and return the ecn number(this may be one number or 100 different numbers). Don't care what field it is in, just the ecn number.

Thanks again for your patience.

Tim

 

[vbaInet]

In that case:

1. Remove the Select Changes.ecn_number line and the Union All line after that
2. For each Select line inside the parens, add the ecn_number field as the third or second field
3. In the WHERE part, change ecn_number to Part_Number_1

 

[tpeter]

If I understood you correctly I didn't see ecn_number in the where clause, I am now getting a syntax error in the from clause:

SELECT *FROM

(select Part_Number_1, "ECN_Number") from Changes

Union All

(select Part_Number_2, "ECN_Number") from Changes

Union All

(select Part_Number_3, "ECN_Number") from Changes

Union All

(select Part_Number_4, "ECN_Number") from Changes

Union All

(select Part_Number_5, "ECN_Number") from Changes

Union All

(select Part_Number_6, "ECN_Number") from Changes

Union All

(select Part_Number_7, "ECN_Number") from Changes

Union All

(select Part_Number_8, "ECN_Number") from Changes

Union All

(select Part_Number_9, "ECN_Number") from Changes

UNION ALL
(select Part_Number_10, "ECN_Number") from Changes
WHERE Part_Number_1 = [Enter Part Number];

 

[vbaInet]

You called ecn_number Prod, so replace prod with Part_Number_1 in the WHERE part.

And I didn't mention that you should add extra parentheses. Also, ecn_number in quotes will just printout ecn_number as text. It needs to be like this [ecn_number]

Finally, I mentioned you should add it as the 2nd or 3rd field but I didn't say you should remove what was already in the 2nd field.

 

[tpeter]

Sorry for the misunderstanding on the parens. Is it correct to put a , then the third? Getting 3 columns but only 2 )Product and Location so code will not run. Starting to get confused.

SELECT *
FROM (select Changes.Part_Number_1 As Prod, "ECN_Number" as Location from Changes

Union All

select Part_Number_1, "Part_Number_1",[ECN_Number] from Changes

Union All

select Part_Number_2, "Part_Number_2",[ECN_Number] from Changes

Union All

select Part_Number_3, "Part_Number_3",[ECN_Number] from Changes

Union All

select Part_Number_4, "Part_Number_4",[ECN_Number] from Changes

Union All

select Part_Number_5, "Part_Number_5",[ECN_Number] from Changes

Union All

select Part_Number_6, "Part_Number_6",[ECN_Number] from Changes

Union All

select Part_Number_7, "Part_Number_7",[ECN_Number] from Changes

Union All

select Part_Number_8, "Part_Number_8",[ECN_Number] from Changes

Union All

select Part_Number_9, "Part_Number_9",[ECN_Number] from Changes

UNION ALL select Part_Number_10, "Part_Number_10",[ECN_Number] from Changes)
WHERE Part_Number_1= [Enter Part Number];

 

[vbaInet]

Do you see your mistake?

select Changes.Part_Number_1 As Prod, "ECN_Number" as Location from Changes