If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Many to Many Relationship Report
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? -- Thank You, Steve |
#2
|
|||
|
|||
Many to Many Relationship Report
=?Utf-8?B?U3RldmUgRA==?= wrote:
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? -- 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 |
#3
|
|||
|
|||
Many to Many Relationship Report
Steve D wrote:
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. -- Marsh MVP [MS Access] |
#4
|
|||
|
|||
Many to Many Relationship Report
Perfect.
-- Thank You, Steve "XPS35" wrote: =?Utf-8?B?U3RldmUgRA==?= wrote: 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? -- 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 . |
Thread Tools | |
Display Modes | |
|
|