Newbie Question

  • Thread starter Thread starter Roman B.
  • Start date Start date
R

Roman B.

I have 10 tables, all of which contain names and each representing a
different group, (FIRST,MIDDLE,LAST, GROUP). Most of the same names appear in
different groups so I'm trying to run a querie and ultimately a report that
would show the persons name once and all the various groups that person may
belong to.

any help would be much appreciated.
 
try something like

select first & middle & last as personname, group
from thetable
order by personname

then in the report based on this query group the data by the
personname field and it will show the persons name then a list of
groups for that person and so on for each person.

Regards
Kelvan
 
actually on second though your query structure makes it very difficult
you shoudl have 1 table which had the persons name and the group does
the actual table contain the group or is it just the table name

the query to get the info is more complex then but the report is the
same

select first & middle & last as personname, group
from table1
union all
select first & middle & last as personname, group
from table2
union all
select first & middle & last as personname, group
from table3
union all
select first & middle & last as personname, group
from table4
union all
select first & middle & last as personname, group
from table5
union all
select first & middle & last as personname, group
from table6
union all
select first & middle & last as personname, group
from table7
union all
select first & middle & last as personname, group
from table8
union all
select first & middle & last as personname, group
from table9
union all
select first & middle & last as personname, group
from table10

hope this helps

Regards
Kelvan
 
I have 10 tables, all of which contain names and each representing a
different group, (FIRST,MIDDLE,LAST, GROUP).
Most of the same names appear in
different groups so I'm trying to run a querie and ultimately a report that
would show the persons name once and all the various groups that person may
belong to.

any help would be much appreciated.
Click to expand...

You are "committing spreadsheet". You'll have much better luck using a
properly normalized table structure, with *three* tables:

Groups
GroupID <Autonumber, Primary Key>
GroupName

People
PersonID <Autonumber, Primary Key>
LastName
FirstName
MiddleName
<other biographical data as needed>

Membership
GroupID <long integer, link to Groups>
PersonID <long integer, link to People>
<any info about this person's membership in this group, e.g. role, date
joined, comments, ...>

You can migrate the data from your current non-normalized design into this
structure using a couple of append queries. Your report will then become much
simpler!
 
Back
Top