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.

 

[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)