Many to Many Relationship Report

[Steve D]

Good Afternoon,
I am hoping this is possible, any help is appreciated:

I have 2 tables:

Table 1
CC# Router#
1002 1
1002 2

Table 2
CC# Switch#
1002 3
1002 4
I am trying to get:

Report 1:
1002
Router
1
2
Switch
3
4

but my query comes out:
CC# Router# Switch#
1002 1 3
1002 1 4
1002 2 3
1002 2 4

Is there a way to get this result?

 

[XPS35]

Good Afternoon,
I am hoping this is possible, any help is appreciated:

I have 2 tables:

Table 1
CC# Router#
1002 1
1002 2

Table 2
CC# Switch#
1002 3
1002 4
I am trying to get:

Report 1:
1002
Router
1
2
Switch
3
4

but my query comes out:
CC# Router# Switch#
1002 1 3
1002 1 4
1002 2 3
1002 2 4

Is there a way to get this result?

First of all you should consider to redesign your 2 tables to 1 one
tabel with 3 fields:
- CC#
- EquipmentType
- Equipment#

You can also "simulate" this with a UNION query:
SELECT CC#, "Router" AS EquipmentType, Router# AS Equipment# FROM T1
UNION
SELECT CC#, "Switch" AS EquipmentType, Switch# AS Equipment# FROM T2

Using this query as source for your report you can group the report by
CC# and EquipmentType.

 

[Marshall Barton]

Good Afternoon,
I am hoping this is possible, any help is appreciated:

I have 2 tables:

Table 1
CC# Router#
1002 1
1002 2

Table 2
CC# Switch#
1002 3
1002 4
I am trying to get:

Report 1:
1002
Router
1
2
Switch
3
4

but my query comes out:
CC# Router# Switch#
1002 1 3
1002 1 4
1002 2 3
1002 2 4

Sounds like the record source query joins the two tables
when you want a union of them:

SELECT tbl1.[CC#], tbl1.[Router#], "Router" As Device
UNION ALL
SELECT tbl2.[CC#], tbl1.[Switch#], "Switch"

The report can group on the CC# field and then on the Device
field.

 

[Steve D]

Perfect.
--
Thank You,
Steve

First of all you should consider to redesign your 2 tables to 1 one
tabel with 3 fields:
- CC#
- EquipmentType
- Equipment#

You can also "simulate" this with a UNION query:
SELECT CC#, "Router" AS EquipmentType, Router# AS Equipment# FROM T1
UNION
SELECT CC#, "Switch" AS EquipmentType, Switch# AS Equipment# FROM T2

Using this query as source for your report you can group the report by
CC# and EquipmentType.

--
Groeten,

Peter
http://access.xps350.com

.