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
15 4255 NeoPa 32,556
Expert Mod 16PB
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).
NeoPa
Still nothin... Any Idea what my PK's should be? Should I have multiple?
You're going to need a crosstab query to get the result in the format you're looking for.
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.
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.
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.
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.
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 qryCorporationOfficers
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
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;
-
-
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 32,556
Expert Mod 16PB 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).
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.
@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!
NeoPa 32,556
Expert Mod 16PB 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.
You can paint it any color you want...
But your first post is the SUM of wrong.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Allen Browne |
last post by:
If you are looking for opinon on what's useful in Access 2007, there's a new
article at:
http://allenbrowne.com/Access2007.html
Covers what's good (useful features), what's mixed (good and bad),...
|
by: nebbiasun |
last post by:
I have a split database which calls a word document (mail merge from
query) which works perfectly in both access 2003 and 2007. I have
secured the frontend and backend (user level security) in 2003...
|
by: ARC |
last post by:
Just curious if anyone is having issues with Acc 2007 once the number of
objects and complexity increases? I have a fairly large app, with many
linked tables, 100's of forms, queries, reports, and...
|
by: TheScullster |
last post by:
Hi all
This must have been covered already, but Google (or my use thereof) is
failing me!
We have a company database originally created in Access 2000.
The backend is located on a Win2003...
|
by: nico5038 |
last post by:
Access 2007 Linkedtable manager refuses to relink tables having a field with the "Attachment" datatype.
Problem:
When placing a split database in another folder, the Linked table manager should...
|
by: tony.abbitt |
last post by:
I have recently installed Office 2007 (SP1) retaining the previous
installation of Office 2003.
I have converted an Access 2003 database to Access 2007. The database
contains the VBA code...
|
by: WPW07 |
last post by:
Hello,
We have several complex applications developed in Access 2003 by
various outside consultants. These applications link to a variety of
Oracle tables and are used only for Access reports. ...
|
by: masonic35and7 |
last post by:
I work for a school district, and I have just imported 6 Excel
worksheets into Access 2007. Now I have 6 different tables. In each
table all the fields are the same. I need the easiest way to...
|
by: prakashwadhwani |
last post by:
Hi !! I'm about to develop a new project for a client.
Should I go about it in Access 2003 or 2007 ? Purchasing it either
for me or for my client is not a major consideration here ... what I'd...
|
by: khoward |
last post by:
Hi,
I have an Access 2007 database that contains customer contact information. There are over 8,000 that include name, organization (as a look-up column), email, phone, address, and events that...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
| |