Crosstab query (1 Viewer)

jsic1210 · Jun 4, 2014

Hello,
I have two tables, and I'm trying to create a crosstab query...I think. One table is Contracts, one is Contacts. Each contract could have multiple contacts, but they are numbered (1, 2, 3...etc.) based on importance. I want to create a query that keeps each contract on one line, and separate fields for each contact and each field of the contact. So a contact will have Title, First, Last, Address, etc. So I want my query to show as follows:

Code:

Contract-----Title1-----First1-----Title2-----First2
ContractX    Mr.        James      Mrs.       Sally

I of course need this to be dynamic, so if a contract has 9 contacts, there are fields up to Title9, First9, etc.
Any suggestions or is this impossible?
Thanks in advance!

CJ_London · Jun 4, 2014

Can you revise your example to include 2 or more contacts since this is the thing that will determine what type of query you need.

Also, from your post it implies your contract table is something like

tblContracts
ContractID
ContractName
Contact1
Contact2
Contact3
etc

Which is a bad design

jsic1210 · Jun 4, 2014

No sorry, it's not like that.
Contracts
ContractID
ContractName

Contacts
ContactID
ContractID (to link to Contracts)
Contact #
Title
First
Last

So if I have one contract with two contacts:
Contracts
ContractID=1
ContractName="ContractX"

Contacts
(first record)
ContactID=1
ContractID=1
Contact # = 1
Title = "Mr."
First = "John"
Last = "Doe"

(second record)
ContactID=2
ContractID=1
Contact # = 2
Title = "Mrs."
First = "Jane"
Last = "Smith"

But I want to show it in the query as I showed in the first post, as if they were their own fields. It's for an Excel export, and really needs to be on one line in Excel.

CJ_London · Jun 4, 2014

you can do it using a cross tab if you are happy to have a field concatenating the title and lastname e.g

Contact
Mr Doe
Mrs Smith

this would be something like

Code:

TRANSFORM First([Title] & " " & [last]) AS Contact
SELECT Contracts.ContractID, Contracts.ContractName
FROM Contracts INNER JOIN Contacts ON Contracts.ContractID= Contacts.ContractID GROUP BY Contracts.ContractID, Contracts.ContractName
PIVOT "Contact" & Contacts.[Contact #] In ("Contact1","Contact2","Contact3"....etc)

jsic1210 · Jun 4, 2014

I did think of that, but it's being used for a mail merge, so certain fields need to be separated.
Right now, I'm seeing if I can convince the user that all contacts don't need to be on one line in Excel. As long as this file isn't being used for reporting, I think simply doing a left join (Contracts to Contacts) and allowing a contract to display on separate lines (whatever the number of contacts is), this should take care of the problem. Hopefully the user agrees.

Thank you for all of your help!

CJ_London · Jun 4, 2014

I would have thought so - you only get one document per line so unless you are sending one letter to multiple recipients as a single document (no copies) you would need each recipient on a separate line anyway

gblack · Jun 4, 2014

There's probably a better way to do this from the get-go... but if you did concatenate these values in your crosstab, you could always use that crosstab query as the basis for another select query and then parse out the [Contact] field with a couple string functions.

Like:
Mid([Contact], 1, Instr([Contact], " ")-1) AS Title,
Mid([Contact], Instr([Contact], " ")+1, Len([Contact]) - Instr([Contact], " ")) AS Last_Name

Then use that query for mail merge and such...

jsic1210 · Jun 6, 2014

CJ_London,
It turns out, only one letter is being sent to each company, with all recipients listed (in order) on that letter. Any suggestions for how I'd do this mail merge? I've honestly never done a mail merge before, so I was having it export to Excel and allowing the user to do the mail merge.

Thanks,
Jeff

Crosstab query (1 Viewer)

jsic1210

Registered User.

CJ_London

Super Moderator

jsic1210

Registered User.

CJ_London

Super Moderator

jsic1210

Registered User.

CJ_London

Super Moderator

gblack

Registered User.

jsic1210

Registered User.

Similar threads

Users who are viewing this thread