Combine Queries?

[Drewman626]

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

[mlcampeau]

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.

[Drewman626]

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!

[mlcampeau]

I don't know what I was thinking when I said Append query. What I meant to say was Union query.
Try:

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]
UNION
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];

I haven't tested this, but if your above queries worked, then something like this should.

[Drewman626]

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.

[mlcampeau]

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?

[Drewman626]

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?

[mlcampeau]

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.

[Drewman626]

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...

:)

[mlcampeau]

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!