Access 2007... Merge two tables

[Clint Nelson]

Thanks to all in advance...
I have two tables...
corporations...
ID corpname corpnumber address
1 widgets 123456 1122 3rd
2 acme 234567 123 4th st
3 engineers 345678 987 wall st
etc...

officers...
ID corpnumber type fname lname
1 123456 pres john baker
2 123456 dir joe blow
3 123456 sec jane doe
4 234567 pres mike jones
5 234567 dir suzy sayz
6 234567 sec paul north
etc...
I need 1 table similar to this...
ID corpname corpnumber address pres dir sec
1 widgets 123456 1122 3rd j.baker j.blow j.doe
2 acme 234564 123 4th m.jones s.sayz p.north
etc...
I've tried different relationships, changing PK's, union query, append query, etc... All I come up with is one officer per corporation...
Any help is appreciated.
Clint

[NeoPa]

Try creating a query with both tables included and using a LEFT JOIN from [Corporations] to [Officers] on Corporations.Co rpNumber = Officers.CorpNu mber.

You don't need a new table for this as the query will do all you should be thinking about with this data. Trust me on this. A new table would be a thoroughly bad idea (Or don't trust me and ask for reasoning - I'm happy either way).

[Clint Nelson]

NeoPa
Still nothin... Any Idea what my PK's should be? Should I have multiple?

[Rabbit]

You're going to need a crosstab query to get the result in the format you're looking for.

[Clint Nelson]

Ummm. I don't want to or need to calculate anything... all I wanna do is sort the company officers(i.e. president, director, secretary) to the respective company into one record.

[Rabbit]

Which is why you would use a crosstab. If you're trying to consolidate multiple records into one record, then you have two options, a crosstab or 3 subqueries. A crosstab would be the quickest of the two options.

[Clint Nelson]

I am kinda new to this... so I am gonna read elsewhere on how to find the SUM of 10,000 last names or the AVG of 10,000 Cities.

[Rabbit]

But you're not trying to find the sum/average of cities/names. All you need is the first(), last(), max(), or min() of the cities/names. You are assuming that a crosstab has to be the mathematical aggregation of numbers when that is not the case.

[Delerna]

Just to verify what as Rabbit says
crosstab is the easiest

I did a mockup db of your data

First you need a query to combine the 2 tables for the crosstab

SELECT corporations.corpname,
   corporations.address,
   officers.type,
   [fname] & ' ' & [lname] AS Name
FROM corporations 
INNER JOIN officers ON
   corporations.corpnumber =officers.corpnumber;
 

I called it qryCorporationO fficers


Now make the crosstab

TRANSFORM First(qryCorporationOfficers.Name) AS FirstOfName
SELECT qryCorporationOfficers.corpname,
   qryCorporationOfficers.address,
   First(qryCorporationOfficers.Name) AS [Total Of Name]
FROM qryCorporationOfficers
GROUP BY qryCorporationOfficers.corpname,
   qryCorporationOfficers.address
PIVOT qryCorporationOfficers.type;
 

and the result is attached

[Delerna]

oops, forgot to remove the total column
Here is revised crosstab

TRANSFORM First(qryCorporationOfficers.Name) AS FirstOfName
SELECT qryCorporationOfficers.corpname,
   qryCorporationOfficers.address
FROM qryCorporationOfficers
GROUP BY qryCorporationOfficers.corpname,
   qryCorporationOfficers.address
PIVOT qryCorporationOfficers.type;