By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,667 Members | 2,261 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,667 IT Pros & Developers. It's quick & easy.

Access 2007... Merge two tables

P: 6
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
Dec 19 '10 #1
Share this Question
Share on Google+
15 Replies


NeoPa
Expert Mod 15k+
P: 31,273
Try creating a query with both tables included and using a LEFT JOIN from [Corporations] to [Officers] on Corporations.CorpNumber = Officers.CorpNumber.

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).
Dec 20 '10 #2

P: 6
NeoPa
Still nothin... Any Idea what my PK's should be? Should I have multiple?
Dec 21 '10 #3

Rabbit
Expert Mod 10K+
P: 12,327
You're going to need a crosstab query to get the result in the format you're looking for.
Dec 22 '10 #4

P: 6
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.
Dec 22 '10 #5

Rabbit
Expert Mod 10K+
P: 12,327
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.
Dec 22 '10 #6

P: 6
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.
Dec 22 '10 #7

Rabbit
Expert Mod 10K+
P: 12,327
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.
Dec 22 '10 #8

Delerna
Expert 100+
P: 1,134
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
Expand|Select|Wrap|Line Numbers
  1. SELECT corporations.corpname,
  2.    corporations.address,
  3.    officers.type,
  4.    [fname] & ' ' & [lname] AS Name
  5. FROM corporations 
  6. INNER JOIN officers ON
  7.    corporations.corpnumber =officers.corpnumber;
  8.  
I called it qryCorporationOfficers


Now make the crosstab
Expand|Select|Wrap|Line Numbers
  1. TRANSFORM First(qryCorporationOfficers.Name) AS FirstOfName
  2. SELECT qryCorporationOfficers.corpname,
  3.    qryCorporationOfficers.address,
  4.    First(qryCorporationOfficers.Name) AS [Total Of Name]
  5. FROM qryCorporationOfficers
  6. GROUP BY qryCorporationOfficers.corpname,
  7.    qryCorporationOfficers.address
  8. PIVOT qryCorporationOfficers.type;
  9.  
and the result is attached
Attached Images
File Type: jpg corp.jpg (26.5 KB, 209 views)
Dec 23 '10 #9

Delerna
Expert 100+
P: 1,134
oops, forgot to remove the total column
Here is revised crosstab
Expand|Select|Wrap|Line Numbers
  1. TRANSFORM First(qryCorporationOfficers.Name) AS FirstOfName
  2. SELECT qryCorporationOfficers.corpname,
  3.    qryCorporationOfficers.address
  4. FROM qryCorporationOfficers
  5. GROUP BY qryCorporationOfficers.corpname,
  6.    qryCorporationOfficers.address
  7. PIVOT qryCorporationOfficers.type;
  8.  
  9.  
Dec 23 '10 #10

Delerna
Expert 100+
P: 1,134
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
Dec 23 '10 #11

NeoPa
Expert Mod 15k+
P: 31,273
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).
Dec 23 '10 #12

Rabbit
Expert Mod 10K+
P: 12,327
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.
Dec 23 '10 #13

P: 6
@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,PK'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...Nevermind
Merry Christmas... and since I'm gonna get kicked outta here have a Happy New Year too!
Dec 23 '10 #14

NeoPa
Expert Mod 15k+
P: 31,273
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.
Dec 24 '10 #15

P: 6
You can paint it any color you want...
But your first post is the SUM of wrong.
Dec 24 '10 #16

Post your reply

Sign in to post your reply or Sign up for a free account.