Tags: access, basically, database, ive, linking, microsoft, mysql, oracle, properties, query, records, sql, tables, withthe

Join Properties - Show all records

On Database » Microsoft Access

5,009 words with 2 Comments; publish: Fri, 30 May 2008 19:53:00 GMT; (25093.75, « »)

I've got a query that is linking 2 tables. However, I'm having problems with

the join properties in linking these tables.

Basically from what I can understand, there are 3 options:

1. Where join field from both tables are equal

2. All records from table 1 and only those from table 2 where they match

3. All records from table 2 and only those from table 1 where they match

How do I go about having all records from both tables showing?

The tables are as follows:

Table 1

Field 1: Department

Field 2: Actual Numbers

Field 3: Actual Income

Table 2:

Field 1: Department

Field 2: Budget Numbers

Field 3: Budget Income

Both tables come from different data sources, otherwise I would just have

one table

There are some Departments (Field 1) in table 1 that aren't in table 2 and

vice versa.

Basically I want my report to have the following columns:

Department--Actual Numbers--Budget Numbers--Actual Income--Budget Income.

For all Departments (Field 1) from both tables.

Any ideas would be very much appreciated

All Comments

Leave a comment...

  • 2 Comments
    • "Clive" <Clive.ms-access.todaysummary.com.discussions.microsoft.com> wrote in message

      news:51FF268A-F55F-4734-8A6B-7CF7188236C1.ms-access.todaysummary.com.microsoft.com...

      > I've got a query that is linking 2 tables. However, I'm having problems

      > with

      > the join properties in linking these tables.

      >

      > Basically from what I can understand, there are 3 options:

      > 1. Where join field from both tables are equal

      > 2. All records from table 1 and only those from table 2 where they match

      > 3. All records from table 2 and only those from table 1 where they match

      > How do I go about having all records from both tables showing?

      > The tables are as follows:

      > Table 1

      > Field 1: Department

      > Field 2: Actual Numbers

      > Field 3: Actual Income

      > Table 2:

      > Field 1: Department

      > Field 2: Budget Numbers

      > Field 3: Budget Income

      > Both tables come from different data sources, otherwise I would just have

      > one table

      > There are some Departments (Field 1) in table 1 that aren't in table 2 and

      > vice versa.

      > Basically I want my report to have the following columns:

      > Department--Actual Numbers--Budget Numbers--Actual Income--Budget

      > Income.

      > For all Departments (Field 1) from both tables.

      > Any ideas would be very much appreciated

      The Jet database engine doesn't support full outer joins, so you have to

      combine a left join with a right join via a union query. The result will

      not be updatable, but if you just want it for a report that should be fine.

      Try something like this:

      SELECT

      [Table 1].Department,

      [Actual Numbers],

      [Budget Numbers],

      [Actual Income],

      [Budget Income]

      FROM [Table 1] LEFT JOIN [Table 2]

      ON [Table 1.Department] = [Table 2.Department]

      UNION ALL

      SELECT

      [Table 2].Department,

      [Actual Numbers],

      [Budget Numbers],

      [Actual Income],

      [Budget Income]

      FROM [Table 1] RIGHT JOIN [Table 2]

      ON [Table 1.Department] = [Table 2.Department]

      WHERE [Table 1].Department Is Null

      Dirk Goldgar, MS Access MVP

      www.datagnostics.com

      (please reply to the newsgroup)

      #1; Fri, 30 May 2008 19:55:00 GMT
    • Thanks Dirk,

      This has got me the furthest so far. However, i'm getting duplicate values

      in the budget. i think i may need to put a max expression against the budget

      figures.

      is it just a case of putting

      Max[Budget Numbers],

      Thanks in advance

      Clive

      "Dirk Goldgar" wrote:

      > "Clive" <Clive.ms-access.todaysummary.com.discussions.microsoft.com> wrote in message

      > news:51FF268A-F55F-4734-8A6B-7CF7188236C1.ms-access.todaysummary.com.microsoft.com...

      >

      > The Jet database engine doesn't support full outer joins, so you have to

      > combine a left join with a right join via a union query. The result will

      > not be updatable, but if you just want it for a report that should be fine.

      > Try something like this:

      > SELECT

      > [Table 1].Department,

      > [Actual Numbers],

      > [Budget Numbers],

      > [Actual Income],

      > [Budget Income]

      > FROM [Table 1] LEFT JOIN [Table 2]

      > ON [Table 1.Department] = [Table 2.Department]

      > UNION ALL

      > SELECT

      > [Table 2].Department,

      > [Actual Numbers],

      > [Budget Numbers],

      > [Actual Income],

      > [Budget Income]

      > FROM [Table 1] RIGHT JOIN [Table 2]

      > ON [Table 1.Department] = [Table 2.Department]

      > WHERE [Table 1].Department Is Null

      > --

      > Dirk Goldgar, MS Access MVP

      > www.datagnostics.com

      > (please reply to the newsgroup)

      >

      #2; Fri, 30 May 2008 19:56:00 GMT