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

Combine Queries?

P: 9
Can someone please help me. I have a feeling I'm making the problem much more complicated than it has to be. I'll start by describing the end goal, then I'll describe my actions to date.

The situation:

You have "tbl_projects" Each project has a primary contact and a secondary contact.

The goal:

View frm_contacts columnar, and for each contact show the Projects where the contact is EITHER the primary OR the secondary contact.

My Actions:

Qry_primproj shows contacts listed as primary on project and the projects id
Qry_secproj shows contact listed as secondary on project and the projects id

How do I set up a query so that it will show ALL contacts, and for each contact, will show the ProjectID for which they are EITHER primary OR secondary contact?

Any info is much appreciated.

Notes: A2K, very limited skill in VB
Sep 19 '07 #1
Share this Question
Share on Google+
9 Replies


Expert 100+
P: 296
So you have the two queries working and you are looking to combine them? You could create an Append query. If you post your sql then we can look at it to see if it would be simple to combine the two queries into one.
Sep 19 '07 #2

P: 9
Qry_BrokersLoans

SELECT [Tbl_Contacts].[ContactID], [Tbl_Contacts].[Prefix], [Tbl_Contacts].[FirstName], [Tbl_Contacts].[MI], [Tbl_Contacts].[LastName], [Tbl_Contacts].[Suffix], [Tbl_Contacts].[CompanyID], [Tbl_Contacts].[Title], [Tbl_Contacts].[MainOfficePhone], [Tbl_Contacts].[MainOfficePhoneX], [Tbl_Contacts].[HomePhone], [Tbl_Contacts].[CellPhone], [Tbl_Contacts].[Fax], [Tbl_Contacts].[Roll], [Tbl_Contacts].[BrokerID], [Tbl_Contacts].[ContactNotes], [Tbl_Loans].[LoanID], [Tbl_Loans].[LoanName], [Tbl_Loans].[LoanType], [Tbl_Loans].[LoanPurpose], [Tbl_Loans].[LoanAmount], [Tbl_Loans].[LoanBroker]
FROM Tbl_Contacts INNER JOIN Tbl_Loans ON [Tbl_Contacts].[ContactID] =[Tbl_Loans].[LoanBroker];


Qry_ClientsLoans

SELECT Tbl_Contacts_1.ContactID, Tbl_Contacts_1.Prefix, Tbl_Contacts_1.FirstName, Tbl_Contacts_1.MI, Tbl_Contacts_1.LastName, Tbl_Contacts_1.Suffix, Tbl_Contacts_1.CompanyID, Tbl_Contacts_1.Title, Tbl_Contacts_1.MainOfficePhone, Tbl_Contacts_1.MainOfficePhoneX, Tbl_Contacts_1.HomePhone, Tbl_Contacts_1.CellPhone, Tbl_Contacts_1.Fax, Tbl_Contacts_1.Roll, Tbl_Contacts_1.ContactNotes, [Tbl_Loans].[LoanID], [Tbl_Loans].[LoanName], [Tbl_Loans].[LoanType], [Tbl_Loans].[LoanPurpose], [Tbl_Loans].[LoanAmount], [Tbl_Loans].[LoanClient]
FROM Tbl_Contacts AS Tbl_Contacts_1 INNER JOIN Tbl_Loans ON Tbl_Contacts_1.ContactID=[Tbl_Loans].[LoanClient];



Tbl_Contacts includes everyone. Tbl_contacts!roll defines if the contacts is a "Broker" or "Client"

The goal is to view the Contact's form page and include ANY loan for which the contact is EITHER the broker OR the client

As you can see, tbl_Loans includes one field for "Broker" where the input will be the contactID, and another field for "Client" where the input will be contactID.

Thanks!
Sep 19 '07 #3

Expert 100+
P: 296
I don't know what I was thinking when I said Append query. What I meant to say was Union query.
Try:
Expand|Select|Wrap|Line Numbers
  1. SELECT [Tbl_Contacts].[ContactID], [Tbl_Contacts].[Prefix], [Tbl_Contacts].[FirstName], [Tbl_Contacts].[MI], [Tbl_Contacts].[LastName], [Tbl_Contacts].[Suffix], [Tbl_Contacts].[CompanyID], [Tbl_Contacts].[Title], [Tbl_Contacts].[MainOfficePhone], [Tbl_Contacts].[MainOfficePhoneX], [Tbl_Contacts].[HomePhone], [Tbl_Contacts].[CellPhone], [Tbl_Contacts].[Fax], [Tbl_Contacts].[Roll], [Tbl_Contacts].[BrokerID], [Tbl_Contacts].[ContactNotes], [Tbl_Loans].[LoanID], [Tbl_Loans].[LoanName], [Tbl_Loans].[LoanType], [Tbl_Loans].[LoanPurpose], [Tbl_Loans].[LoanAmount], [Tbl_Loans].[LoanBroker]
  2. FROM Tbl_Contacts INNER JOIN Tbl_Loans ON [Tbl_Contacts].[ContactID] =[Tbl_Loans].[LoanBroker]
  3. UNION
  4. SELECT Tbl_Contacts_1.ContactID, Tbl_Contacts_1.Prefix, Tbl_Contacts_1.FirstName, Tbl_Contacts_1.MI, Tbl_Contacts_1.LastName, Tbl_Contacts_1.Suffix, Tbl_Contacts_1.CompanyID, Tbl_Contacts_1.Title, Tbl_Contacts_1.MainOfficePhone, Tbl_Contacts_1.MainOfficePhoneX, Tbl_Contacts_1.HomePhone, Tbl_Contacts_1.CellPhone, Tbl_Contacts_1.Fax, Tbl_Contacts_1.Roll, Tbl_Contacts_1.ContactNotes, [Tbl_Loans].[LoanID], [Tbl_Loans].[LoanName], [Tbl_Loans].[LoanType], [Tbl_Loans].[LoanPurpose], [Tbl_Loans].[LoanAmount], [Tbl_Loans].[LoanClient]
  5. FROM Tbl_Contacts AS Tbl_Contacts_1 INNER JOIN Tbl_Loans ON Tbl_Contacts_1.ContactID=[Tbl_Loans].[LoanClient];
I haven't tested this, but if your above queries worked, then something like this should.
Sep 19 '07 #4

P: 9
Dude!

You rock!

I didn't use your exact code because it looked like something was missing, and I had to delete an un-matching column from one of the queries, but I had no idea you could UNION two tables/queries like this.

The resulting query is exactly what I wanted and I've used a subform (don't kill me) in the Contacts form that lists loans this contact is associated with.

Excellent.

Thank you.

If you have another suggestion as to how I could or should include this information on the Contact form without using a subform, i'm all ears.
Sep 19 '07 #5

Expert 100+
P: 296
Dude!

You rock!

I didn't use your exact code because it looked like something was missing, and I had to delete an un-matching column from one of the queries, but I had no idea you could UNION two tables/queries like this.

The resulting query is exactly what I wanted and I've used a subform (don't kill me) in the Contacts form that lists loans this contact is associated with.

Excellent.

Thank you.

If you have another suggestion as to how I could or should include this information on the Contact form without using a subform, i'm all ears.
Yes, I did notice that your one query had an un-matching column, but it was more the idea I was trying to convey. I'm glad you got it to work!
As for the subform part, is your contact form and subform based on the same query?
Sep 19 '07 #6

P: 9
Yes, I did notice that your one query had an un-matching column, but it was more the idea I was trying to convey. I'm glad you got it to work!
As for the subform part, is your contact form and subform based on the same query?

No... :(

It's a small query with a specific goal in mind. I guess I could use it as part of a larger query, but jumping ahead, the problem I would get to is that I don't know how I would set it up for a one to many. One broker will have many loans. I'd have to list all of the loans with a link to the loan form on the broker's ContactForm (or something like it.) I don't know how to do that without using a tabular subform.

Thoughts?
Sep 19 '07 #7

Expert 100+
P: 296
I would probably use a subform in this instance. Seems the most logical way to do it. I'm not sure if there's another way. At least not another simple way of doing it. I'm still learning a lot when it comes to Access, so unfortunately, other than using a subform, I have no other ideas.
Sep 19 '07 #8

P: 9
I would probably use a subform in this instance. Seems the most logical way to do it. I'm not sure if there's another way. At least not another simple way of doing it. I'm still learning a lot when it comes to Access, so unfortunately, other than using a subform, I have no other ideas.

That's fine. Your UNION advice was killer. I can think of so many uses for that. I'm now rethinking redoing about half my tables and queries... thanks a lot...

:)
Sep 19 '07 #9

Expert 100+
P: 296
That's fine. Your UNION advice was killer. I can think of so many uses for that. I'm now rethinking redoing about half my tables and queries... thanks a lot...

:)
Not a problem! Good luck with the rest of your project!
Sep 19 '07 #10

Post your reply

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