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
[code=sql]
SELECT corporations.co rpname,
corporations.ad dress,
officers.type,
[fname] & ' ' & [lname] AS Name
FROM corporations
INNER JOIN officers ON
corporations.co rpnumber =officers.corpn umber;
[/code]

I called it qryCorporationO fficers


Now make the crosstab
[code=sql]
TRANSFORM First(qryCorpor ationOfficers.N ame) AS FirstOfName
SELECT qryCorporationO fficers.corpnam e,
qryCorporationO fficers.address ,
First(qryCorpor ationOfficers.N ame) AS [Total Of Name]
FROM qryCorporationO fficers
GROUP BY qryCorporationO fficers.corpnam e,
qryCorporationO fficers.address
PIVOT qryCorporationO fficers.type;
[/code]

and the result is attached

[Delerna]

oops, forgot to remove the total column
Here is revised crosstab
[code=sql]
TRANSFORM First(qryCorpor ationOfficers.N ame) AS FirstOfName
SELECT qryCorporationO fficers.corpnam e,
qryCorporationO fficers.address
FROM qryCorporationO fficers
GROUP BY qryCorporationO fficers.corpnam e,
qryCorporationO fficers.address
PIVOT qryCorporationO fficers.type;

[/code]

[Delerna]

This will only work if each company never has more than one sec,pres or dir

If they do then the subquery route would possibly be easier

[NeoPa]

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

What does that mean? You've still done nothing? Whatever you've done and tried, telling us nothing about it leaves us in no position to help you further.

If you'd like to explain exactly what you did and what results you got we may be able to be of more assistance to you. Without further intelligible comment from you I can merely draw your attention to a thread that tells how to aggregate the data from multiple records into a single string (Combining Rows-Opposite of Union).

[Rabbit]

Well, I don't know about easier. The concept of a crosstab is probably more difficult to grasp than a subquery. But it would certainly run faster than subqueries.

[Clint Nelson]

@Neopa... 2 Days later... No I did nothing with a crosstab query, let alone an INNER join.
@Rabbit... Like I said, I am new to this. Your statement regarding crosstabs being hard to grasp... WOW, I had more errors than a little league baseball team on opening day.
@Delerna... I was just expecting a link to some sample code or a little more insight into types of query's, multiple relationships,P K's...etc..
You went well out of your way, and 2 hrs later, I am good to go. I can't thank you enough.
I didn't mean to ruffle any feathers, but if your gonna give advice...Neverm ind
Merry Christmas... and since I'm gonna get kicked outta here have a Happy New Year too!

[NeoPa]

Clint Nelson:
@Neopa... 2 Days later... No I did nothing with a crosstab query, let alone an INNER join.

Well I'm so sorry I didn't reply sooner. I guess I just forgot about how unimportant my life is compared to answering your question for you.

BTW I never suggested using CrossTabs for this. It would not be the preferred solution for my money.

I was simply trying to point you in the right direction, but was hampered by the lack of intelligent response on your part (Ref post #3).

For want of anything to work with I gave you a link to a thread I believed (and still believe) would solve your problem. From your response I would guess you didn't bother to look at it even. Never mind. I'm sure others reading this thread with a similar problem won't have that difficulty, and my time won't have been entirely wasted.

It's been a blast. You must come back again.