Parameter Query Selection Problem

I have a database table with 12 item number fields (and each has a corresponding quantity field) in it.  Each can be a different item number.  (example: Item number 1, Quantity 1: Item number 2, Quantity 2, etc)Is there a way to create a query to select an item and qty using a parameter in the query not tied to any field and using this parameter to select all items that match this parameter?  To further explain, lets say I want to select Item number 1111-04 from this table.  Record 1 would have this item in the Item number 1 field and record 3 would have this item in Item number 4 field.  I would want to select the matching items for this query and list each match separately or in total.    
LMAN49Asked:
Who is Participating?
 
peter57rConnect With a Mentor Commented:
I'm afraid most developers would regard the table design you have described as fundamentally flawed, and you will forever have the same sort of problem you are dealing with here.

There is no 'match all' feature of the type you are asking for.

If you want the whole record displayed for any matches then you will have to test each field with the required value.
In the query grid each match value will have to go onto a different row, to create the OR operator for your matches.
If you are uysing a parameter field then the same parameter field must be used each time on successive rows.

0
 
LMAN49Author Commented:
I understand that I will need to reference the value of the parameter for each of the item number fields in the table.  How do I create this value independent of any field?  After it is created, how do I use it to select the item number I need?  
0
 
peter57rConnect With a Mentor Commented:
How are you creating the query?
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
LMAN49Author Commented:
Yes, I am creating it.
0
 
LMAN49Author Commented:
I created the query with all fields, but how do I create the search parameter for each item field 1 - 12?
I tried creating a field using expression builder with a user entered search parameter, but that did not work correctly.  
0
 
Jeffrey CoachmanConnect With a Mentor MIS LiasonCommented:
LMAN49,

< How do I create this value independent of any field?>
As far as I can see, you can't.
You would need a parameter prompt for each of the 12 fields. (Meaning you would have to type the same creteria 12 time without any mis-spellings!)
Another issus here is that if you plan on using the count of the records returned as your Item count, then I am hoping that one item *can't* exsist in the same row more than once.
This would throw your count off for each row that this happens in.

Besides that, I really don't see how you could get anything meaningfull out of this query, because each record returned would have other Items in it as well.

Complicating things more is the fact the you can't Filter "Columns" dynamically to hide the Misc records.

The query would also have to be built as an "OR" query.

This query would "blow up" as soon as you added another column.
This query would "Blow up" as soon as someone wanted to sum the Quantities for each Item.

IMHO, your best bet is to use the Office links (Tools-->Office Links-->Analyze with Excel) and move this to Excel, where this type of summary is easier to do.

Just my 2c

Lets see if Peter has anything up his sleeve.

JeffCoachman
0
 
LMAN49Author Commented:
OK.  Maybe I need to change the design of the database.  This is a Return Authorization database with one table having all the return info except the items returned.  I use the RA number as the primary key.  I have allowed a total of 12 items to be returned with each RA.  The other table has fields for all 12 items per record.  I have designed a form that Iusing a query to associate the fields properly.  If I could have one record per item in the items database and be able to reference these multiple records on the form, that would solve this problem.  I am not sure how this design, if possible, would work.  
0
 
peter57rConnect With a Mentor Commented:
Well that sounds like a better design. You would probably use a subform to show the list.

However, I don't see why my previous suggestion would not work (unless Access refuses to deal with so many ORs)
If you want to find records with a specifc ID anywhere in 12 fields you just(!) use the same parameter field for every column.  You will only be asked once for the value.

0
 
LMAN49Author Commented:
I will try this sugesstion.  Also, I need to be able to select a particular item number or numbers for statisical information, such as, selecting a specific reason for return or selecting by item and listing the reasons they were returned.  Can a query be created that will break out the items to a separate table selecting only the info I need?
0
 
LMAN49Author Commented:
I have changed the design for the RA datbase to a one to many table.   To explain: each item is in the table by RA Number as opposed to all items in one table per RA Number.  The RA Number should be the key for this table, but I can't get it to select "No duplicates", so I do not have the RA Number field indexed to accept duplicates.  I created a subform using this table, but it will not select the items from this table.
I also need to make sure that it will update the items table properly each time changes are made to the item information.
0
 
LMAN49Author Commented:
The last entry was never answered.  This question is still open and not resolved.
0
 
Jeffrey CoachmanMIS LiasonCommented:
LMAN49,

If the issue is still not resolved, then wiy did you accept our answers?

Jeff
0
All Courses

From novice to tech pro — start learning today.