Multi-Field Parameter Query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have an Assests table and a Software table. In the Assets table, there are
7 fields (software1, software2...)for an opererator to enter the various
software. Someone could enter Word in software1 field, Excel in software2
field. They could also enter Excel or word in any of the other software
fields. The software table lists all the software types with an assocaited
autonumber.

I'd like to create a parameter query to ask 'who has Word'? ,(for example),
with one pop up box.

I'm having problems creating the correct relationships.

I don't want 7 pop ups.

Would a Union Query be the best route?

Thanks!
 
Normalizing your data should be your first step. A table design that has
"software1", "software2", ..."software7" is going to be nothing but trouble.
The simplest data retrieval task becomes an extreme aggravation, as you are
discovering.

Also: what if someone has more than 7 pieces of software? Add another field?
or 10? A design that gracefully supports what you consider "exceptions"
today, saves tons of rework later. (During requirements gathering I try to
assume that anyone who tells me "that will never happen" is wrong, because
75% of the time they are).

SoftwareAssets table
SoftwareAssetID (Autonumber PK)
EmployeeID - ? relates to Assets or Employees table? (not sure what is
stored there)
SoftwareTypeID - relates to SoftwareTypes table
(?create a unique CombinationIndex on EmpID & SoftwareTypeID?)
If an Employee has 1 piece of software, they'll have one record. If they
have 20 pieces, 20 records.

Finding out "who has Word" would be simple with this construction.
Displaying an employees "assets" would also be simple via a continuous
subform/subreport.

HTH,
 
Back
Top