New linked table or not

[m stroup]

Trying to discern better design.

tblDoc
DocNum Text
Title Text
Aircraft A (yes/no)
Aircraft B (yes/no)
Aircraft C (yes/no)
...Aircraft Z (yes/no)

OR

tblDoc
DocNum (text)
Title (text)

tblDocAircraft
xOverID (Autonumber)
DocNum (text)
Aircraft Type (text)

My struggle with the second option (which I think might be a better design)
is that I want to be able to show the aircraft type in each doc using
checkmarks to make the report/form more visual. Is this possible with the
second option?

I also want to pull a report of docs by aircraft type.

any input is appreciated.

 

[orange]

Pax,

Maybe I'm missing something, but it looks like you have a missing table. Or perhaps you just haven't shown it.

Consider

tblDocs Documents
tblAircraft Aircraft(types) etc
tblDocAircraft Relation of Documents to Aircraft

Your option 2 seems a better relational design.

A query against the 3 tables would be along this format.

Select tblDocs.title,tblAircraft.Name,.... from tblDocs,tblAircraft,tblDocAircraft
where
tblDocs.id = tblDocAircraft.id AND
tblAircraft.Type =tblDocAircraft.type

Hope this is helpful.

 

[Klatuu]

Never design a database schema based on what you want on a report. The
second design is the correct way to do it. The first design will create more
problems than it will solve.

If you can provide some more detail on how you want to present the report
and what is the rule that decides which aircraft are shown on a document and
which should have check marks, perhaps we can provide some assistance with
that.

 

[Piet Linden]

Never design a database schema based on what you want on a report.  The
second design is the correct way to do it.  The first design will create more
problems than it will solve.

If you can provide some more detail on how you want to present the report
and what is the rule that decides which aircraft are shown on a document and
which should have check marks, perhaps we can provide some assistance with
that.

--
Dave Hargis, Microsoft Access MVP

Trying to discern better design.

tblDoc
   DocNum  Text
   Title        Text
      Aircraft A    (yes/no)
      Aircraft B    (yes/no)
      Aircraft C    (yes/no)
   ...Aircraft Z    (yes/no)

tblDoc
  DocNum   (text)
  Title        (text)

tblDocAircraft
  xOverID         (Autonumber)
  DocNum        (text)
  Aircraft Type (text)

My struggle with the second option (which I think might be a better design)
is that I want to be able to show the aircraft type in each doc using
checkmarks to make the report/form more visual.   Is this possible with the
second option?

I also want to pull a report of docs by aircraft type.

any input is appreciated.

Dave,
I agree... unless you want to get REAL familiar with writing union
queries... and cry when your database performance is absolutely
abysmal.... been there, done that!!!

 

[Beetle]

i would say that you need three tables since, presumably,
an Aircraft Type can be related to more than one Doc;

tblDoc
DocNum (text)
Title (text)

tblAircraft
AircraftID (Autonumber)
AircraftType (text)

tblDocAircraft
xOverID (Autonumber)
DocNum (text)
AircraftID (number)