Access 2007 3 table query: Contact table, Event table, Payment table. Contact may have an Event but no Payment, a Payment but no Event, or an Event and Payment. My query is duplicating the Event and Payments. I have attached an example: The Contact Smith has 2 Events (Rome night and Men club) and 2 Payments ($4000 and $100) that are not associated. I want the query to return 4 records: 2 showing the Payments with associated Contact info (with nothing in the Event fieelds) and 2 showing the Events with associated Contact info (with nothing in the Payments fields.
I can copy and paste SQL with instruction but don't know how to write it.
** Edit **
Fundamentally, you need to join the Event and Payment data together by using a UNION query, then JOIN this data to your Contact table on the right side of a LEFT JOIN. The Event and Payment data in the UNION query part would need to occupy separate fields.
6 2598
Why not just create two queries in the query wizard, one to show events and one to show payments?
Wizard is easiest for simple queries I think.
I need one query that shows ALL Contacts and ANY Payments and/or ANY Events they may have.
Go into the SQL view and copy/paste your SQL.
cheers,
- SELECT DISTINCTROW [CONTACT DEMOGRAPHICS].CID, [CONTACT DEMOGRAPHICS].SOURCE, [CONTACT DEMOGRAPHICS].LastName, [CONTACT DEMOGRAPHICS].FirstName, [CONTACT DEMOGRAPHICS].Address, Event.EID, Event.[Contact ID Number] AS [Event_Contact ID Number], Event.[Date OF Event], Payments.PID, Payments.[CONTACT ID NUMBER] AS [Payments_CONTACT ID NUMBER], Payments.DatePaymentEntered, Payments.PaymentAmount, Payments.DirectFundsTo, IIf([paymentamount]=4000,"",[name of event]) AS Expr1
-
FROM ([CONTACT DEMOGRAPHICS] LEFT JOIN Payments ON [CONTACT DEMOGRAPHICS].CID = Payments.[CONTACT ID NUMBER]) LEFT JOIN Event ON [CONTACT DEMOGRAPHICS].CID = Event.[Contact ID Number];
NeoPa 32,556
Expert Mod 16PB
Fundamentally, you need to join the Event and Payment data together by using a UNION query, then JOIN this data to your Contact table on the right side of a LEFT JOIN. The Event and Payment data in the UNION query part would need to occupy separate fields.
NeoPa - got it right!!!! thanks.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: rickcross |
last post by:
I am trying to use the Access 2007 runtime. I have a program that is
fully working in 2007 but when I install the runtime version with same
Operating system and Access 2003 installed I have...
|
by: LadyIlsebet |
last post by:
I'm not a fantastic Access developer, but I'm trying to help get
Inventory and whatnot organized at work. They are used to 5 year
budget plans that list out exactly what has to be purchased what...
|
by: Sport Girl |
last post by:
Hi everybody , I have this query in SQL using Access 2007:
SELECT Count(Opportunities.Won) AS CountOfWon, Count(Opportunities.Lost) AS CountOfLost, Count(Opportunities.) AS ,...
|
by: lisacrowe |
last post by:
I have a simple database recording complaints. A crosstab query is based on a query which returns resolved complaints only. The crosstab has the field Complaint Type as a row heading and Outcome as a...
|
by: sphinney |
last post by:
Basic question:
Does anyone know how to go about adding a control to an Access 2007 form that will allow viewing a Word 2007 document?
Reason for asking:
My office is about to receive 100+/-...
|
by: Anthony97 |
last post by:
I ran this code on my access 07 db and it updates a number of records 61,425, I try and run the query in SQL Server 2005 modifying the IIf to CASE WHEN and I get a number of sytax errors.
...
|
by: brat33 |
last post by:
I am trying to modify some code to create a mail merged label document within Word 2007, using a Access 2007 Parameter Field. My issue comes about when I cannot see the Parameter Query within the...
|
by: mulamootil |
last post by:
Hey guys - Is there a way to wrap the text in the header field in an Access 2007 table. I did some googling, however no luck, thought I will post it here. I sure appreciate your time and help.
stan
|
by: sentimental37 |
last post by:
I want to create a table in access 2007 by query not in design view. My table should look like;
Serial AutoNumber
Name Text
Country Text
I am using the following query:
CREATE TABLE Details...
|
by: enia atsir |
last post by:
Hi,
I have built a cross tab query Account_ID as row heading and month_year as column heading.
I need to find if an account is at one year and not in the next month.For example: 07_2010 is not...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
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: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
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: 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...
| |