?
Solved

Parameter Query Selection Problem

Posted on 2008-06-09
12
Medium Priority
?
227 Views
Last Modified: 2013-12-25
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.    
0
Comment
Question by:LMAN49
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 3
  • 2
12 Comments
 
LVL 77

Accepted Solution

by:
peter57r earned 900 total points
ID: 21744888
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
 

Author Comment

by:LMAN49
ID: 21744954
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
 
LVL 77

Assisted Solution

by:peter57r
peter57r earned 900 total points
ID: 21745104
How are you creating the query?
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

Author Comment

by:LMAN49
ID: 21745143
Yes, I am creating it.
0
 

Author Comment

by:LMAN49
ID: 21747186
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
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 600 total points
ID: 21747778
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
 

Author Comment

by:LMAN49
ID: 21751417
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
 
LVL 77

Assisted Solution

by:peter57r
peter57r earned 900 total points
ID: 21752264
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
 

Author Comment

by:LMAN49
ID: 21752788
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
 

Author Comment

by:LMAN49
ID: 21803967
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
 

Author Comment

by:LMAN49
ID: 21902764
The last entry was never answered.  This question is still open and not resolved.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 21971599
LMAN49,

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

Jeff
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Suggested Courses
Course of the Month12 days, 2 hours left to enroll

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question