How do I query names that may appear in only one of a possible several fields

  • Thread starter Thread starter Steve
  • Start date Start date
S

Steve

Hope this subject matter makes a refreshing change from businesss matters !
I have a modelling stocks database, to keep track of all the items I possess
that go towards making one aircraft !
Now take an example. I buy a canopy set with canopies for Spitfire,
Hurricane, Lancaster.
I enter the details in a new record and in a field called
ForAircraftType1 using a combo list I select Spitfire, then in field
ForAircraftType2 I select Hurricane, then in ForAircraftType3 I choose
Lancaster and so on.
I have seven of these fields to cope with one cockpit set that had seven
types supplied for, as I stick to one record per purchase.
Then I buy a set of wheels for a Hurricane model so this time just select
Hurricane in ForAircraftType1
I buy some RAF national markings which the hurricane as well as others used,
and select in ForAircraftType1 All_RAF
I buy a set of Sutton seat harnesses and in ForAircraftType1 choose
RAF_Fighters

It even shows scans of the items, I am pleased with that aspect I have
managed to create with some help from here in the past and a good friend !

AND NOW MY PROBLEM:
I want to run a query to show what items I have for Hurricane

If I put "Hurricane" in the Criteria line for field ForAircraftType1 it
shows me e.g. 12 records,
If I then put "Hurricane" in the 'or' cell ForAircraftType2 I still get
12 records, and don't see the canopy set so its not seeing the hurricane
entry in the ...Type2 field. but this 'or' cell entry seems the right
thing to do but ?????
And what of the All_RAF...do I put that in the 'or' cell of
ForAircraftType1
Likewise for RAF_Fighters, I put that in the 'or' cell of ForAircraftType1
but still the result set shows just the 12 records


What do I need to do, if it involves a string of code please type out what
it should be and say where it goes as code is not my strong point !!

Hope someone can help
Thanks
Steve
 
You have a data model problem.
Instead of ForAircraftType1, ForAircraftType2, etc, this data should be
split to another table, that is a child to the parts table.

tblPart
PartID
PartSN
PartName

tblPart_Aircraft
PA_ID
PartID
AircraftID

Being a motorcycle rider and racer, the delima is the same, as a Yamaha R6
Brake lever also fits a Suzuki SV650.

--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
 
Steve,
Thanks for helping, I seem to have rusted up mentally not having
designed or doctored my dbase for sometime,...am I right to say I need
to create a relationship in your example between the PartID field in
both tables, (or in my dbase this must be ItemTitle, which can be
lengthy often including a product Number), and make it that it shows
all records from TblPart_Aircraft (child)to each of tblPart
(parent)...(option 3 in the list).

What is stumping me is when I am entering details via form view for
the TblPart, I would need it to 'pop-up' or whatever, the means of
indicating which aircraft the part is for, I then enter the first
Aircraft, hit new record and enter the next aircraft and so on, so I
enter several aircraft types, all for that one part, then close the
pop-up or whatever I should use. Obviously the critical thing here is
to have it put into the PartID field the exact same title as in the
TblPart, will it do this automatically or is it up to me to type this
in again, its lengthy and that seems a repetition of data entry, very
prone to a different entry error which then causes a relationship
failure. Not sure how this works, it can't work on UniqueNumber as
each child has a unique number which would not match the Parent, so it
must be the title field, the only unique identifier. If it's a
question of launching another form later on and entering the aircraft
types, then its definitely prone to error, some parts may get mislaid
in the meantime.

I tried a form wizard which enabled me to use fields from two tables,
but the result was not visible in normal view, only in design ! I
looked at pop-up forms but could see no way of relating them to input
data into the second table.

A test dbase where I carefully entered the PartID (aka ItemTitle) in
each table managed to produce the desired resultset in a Query,
Criteria line reading "Lancaster" Or "All RAF" Or "RAF Bombers"
worked, THANK YOU !!! a lot simpler, but a pain having to ensure the
title in PartID of TblIntendedAircraft is a perfect clone and flitting
between two open tables a pain !

....HELP !

Steve
 
Back
Top