Prev: Suppress printing group footer using Calculated field(s)
Next: Business Cards listing training records
From: Steve D on 17 May 2010 15:57
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
From: XPS35 on 17 May 2010 16:14
=?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

From: Marshall Barton on 17 May 2010 18:55
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]
From: Steve D on 18 May 2010 10:02
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
>
> .
>
 | 
Pages: 1
Prev: Suppress printing group footer using Calculated field(s)
Next: Business Cards listing training records