Need to query multiple fields within a single table entry and can't

[jamiep]

Hi,

Let me begin by saying that after spending a morning looking for an answer within this forum, it's obvious you folks know one heck of a lot more about MSAccess than I do! Unfortunately, that's evident right now as I'm faced with some difficulties that I need to solve quickly.

Basically, I'm trying to create a database where 1 to 20 part numbers (PartNumber1 through PartNumber20) will be entered in a single table entry (each one will be a work order), along with other information that is specific to each part number (date due, supplier, etc.). I need to be able to find all work orders containing a particular part number and pull that info as a report, but I can't seem to write a query that will look at more than one "blank" at a time.

I've gotten around this temporarily by limiting the part numbers per work order to 6 and creating a monster query that looks at each one at a time, but Access limits the 'or' lines to 6...hopefully I've given you enough info to help me? Thanks in advance for any assistance you can give!

Jamie

 

[DougP]

Lets start out with some relational database 101.

Instead of your table having 6 fields that are part numbers.
have one part number and make multiple records

Like so
I think you are doing it this way.

Part1 part2 part3 part4 etc ... Order Number Date ....
2134 3883 9834 8732 9234455 12/13/00

do this instead.
PartNumber Date OrderNumber
2134 12/13/00 9234455
3883 12/13/00 9234455
9834 12/13/00 9234455
8732 12/13/00 9234455

So each part number is another record.
Then each order can have 1 to many parts
And creating your query is very easy cause you are looking at one field for part number and one field for order number

DougP, MCP
dposton@universal1.com

Ask me how Bar-codes can help you be more productive.

 

[jamiep]

Doug,

I can see I'm not explaining myself well...this is a prototype (read job-shop) environment, so there is probably only one work order ever for a given part number...however, there are multiple part numbers per work order. Consequently, I need to be able to find a given work order by part number, but that means looking through 20 different fields for each work order...and I can't figure out how to get MSAccess to look through 20 fields for a given key number...does that make more sense?

That being said, thanks for your reply, and if I WERE in a typical environment, that would certainly work!

Jamie

 

[Guest_imported]

see some of the examples of looping through fields
real rough example follows!

dim x as field, rst as recordset
set rst = me.recordsetclone
rst.movefirst
do until rst.eof
for each x in rst
if rst.x = "Your criteria" then what ever
next
rst.movenext
loop
rst.close

 

[JonP46]

I may not understand the question, but it seems that workOrder and partNumber should be in separate tables. The workOrder table would contain date, worker, etc. and the partNumber table would contain part number, manufacturer, etc..

By creating a third table for association (workOrderPart) you could eliminate your problem. A one-to-many relationship could be created from partNumber ot workOrderPart -- A one-to-many relationship could also be created from workOrder to workOrderPart.

By doing this you could query part number by work order and visa-versa.

Jon