Solved

Parameter Query Selection Problem

Posted on 2008-06-09
12
184 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
  • 7
  • 3
  • 2
12 Comments
 
LVL 77

Accepted Solution

by:
peter57r earned 300 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 300 total points
ID: 21745104
How are you creating the query?
0
 

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 200 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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

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 300 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

760 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now