need help querying two tables

  • Thread starter Thread starter George R.
  • Start date Start date
G

George R.

Hi, I'm really stuck with something and I'm hoping someone here can
help.

I have one table with customers and and another with customer
transactions. There are also two types of transactions; product
purchases and membership purchases. If membership is purchased there
is a expiry date save with the transaction record, otherwise the
expiry date field is left empty. I have a client id field joining the
two tables.

I'm trying create a query that returns "all" customer names and
address information from the customers table, and also returns
membership expiry dates from the transaction table if one exists for
the customer. If no membership expiry date exists for the customer I
still want there information returned in the query though, just with
no expiry date.

I've tried many ways but don't seem to be able to figure it out.

Could anyone help?

Thank you,

George
 
I think this might work for you.
1) create query that selects membership transactions with
expiration dates from transaction table.
2) create new query, left join customers table to
membership query. (Change the join type to select all
from customers and only those from query that match)
 
Sounds like an application for LEFT JOIN which pulls all records from
Customers even if no corresponding Transaction records exist. Can you give
an example of queries you have tried?
 
Yes, that worked very well for me. Thank you for the suggestion.

My only problem with this was I want just the most recent membership
transaction, yet if a customer has had five membership transactions
over the years then the subquery returns all five. I tried "DISTINCT"
but seeing as the expiry date would change for each membership
transaction the five records were distinct. So what I did was sort the
expirydate field in the subquery to descending, and returned just the
first row: "SELECT TOP 1 ..."

So the problem is solved, and everything seems to be working great
now.

Thank you very much for your time.

George
 
I don't know what I'm talking about. I thought I did when I was
testing with several memberships for just one customer, but as soon as
I added other memberships I realized I was still just getting that one
top record back. hehe

So I still have the problem of getting back more than one record for a
customer if they have purchase more than one membership over the
years. And distinct doesn't change anything because the expiry dates
are different and that makes each individual record distinct. I'm
using this for a report and I don't want to be repeating customer
information in it.

Any Ideas?

here's the sub query:

SELECT tblTransactions.ClientID, tblTransactions.TransactionType,
tblTransactions.MembershipExpiryDate
FROM tblTransactions
WHERE (((tblTransactions.TransactionType)="Membership"))
ORDER BY tblTransactions.MembershipExpiryDate DESC;

here is my query:

SELECT tblClients.ClientID, tblClients.FirstName, tblClients.LastName,
tblClients.Address1, tblClients.Address2, tblClients.Address3,
[tblClients].[City] & ", " & [tblClients].[ProvinceStateAbbr] AS
CityProvince, tblClients.PhoneHome,
subqryClientsList.MembershipExpiryDate
FROM tblClients LEFT JOIN subqryClientsList ON tblClients.ClientID =
subqryClientsList.ClientID
ORDER BY tblClients.LastName;

Thanks,
George
 
Try this for your subquery:

SELECT tblTransactions.ClientID,
tblTransactions.TransactionType, Max
(tblTransactions.MembershipExpiryDate)
FROM tblTransactions
WHERE (([tblTransactions.TransactionType]="Membership"))
GROUP BY tblTransactions.ClientID,
tblTransactions.TransactionType;
-----Original Message-----
I don't know what I'm talking about. I thought I did when I was
testing with several memberships for just one customer, but as soon as
I added other memberships I realized I was still just getting that one
top record back. hehe

So I still have the problem of getting back more than one record for a
customer if they have purchase more than one membership over the
years. And distinct doesn't change anything because the expiry dates
are different and that makes each individual record distinct. I'm
using this for a report and I don't want to be repeating customer
information in it.

Any Ideas?

here's the sub query:

SELECT tblTransactions.ClientID, tblTransactions.TransactionType,
tblTransactions.MembershipExpiryDate
FROM tblTransactions
WHERE (((tblTransactions.TransactionType)="Membership"))
ORDER BY tblTransactions.MembershipExpiryDate DESC;

here is my query:

SELECT tblClients.ClientID, tblClients.FirstName, tblClients.LastName,
tblClients.Address1, tblClients.Address2, tblClients.Address3,
[tblClients].[City] & ", " & [tblClients]. [ProvinceStateAbbr] AS
CityProvince, tblClients.PhoneHome,
subqryClientsList.MembershipExpiryDate
FROM tblClients LEFT JOIN subqryClientsList ON tblClients.ClientID =
subqryClientsList.ClientID
ORDER BY tblClients.LastName;

Thanks,
George

"Les" <[email protected]> wrote in
message news: said:
I think this might work for you.
1) create query that selects membership transactions with
expiration dates from transaction table.
2) create new query, left join customers table to
membership query. (Change the join type to select all
from customers and only those from query that match)
transactions;
product field
joining the one
exists for the
customer I figure
it out.
Click to expand...
.
Click to expand...
 
Sorry for the typo. Where clause shouldn't have [ ], but
( ) instead.
-----Original Message-----
Try this for your subquery:

SELECT tblTransactions.ClientID,
tblTransactions.TransactionType, Max
(tblTransactions.MembershipExpiryDate)
FROM tblTransactions
WHERE (([tblTransactions.TransactionType]="Membership"))
GROUP BY tblTransactions.ClientID,
tblTransactions.TransactionType;
-----Original Message-----
I don't know what I'm talking about. I thought I did
Click to expand...
when
I was
testing with several memberships for just one customer, but as soon as
I added other memberships I realized I was still just getting that one
top record back. hehe

So I still have the problem of getting back more than
Click to expand...
one
record for a
customer if they have purchase more than one membership over the
years. And distinct doesn't change anything because the expiry dates
are different and that makes each individual record distinct. I'm
using this for a report and I don't want to be repeating customer
information in it.

Any Ideas?

here's the sub query:

SELECT tblTransactions.ClientID, tblTransactions.TransactionType,
tblTransactions.MembershipExpiryDate
FROM tblTransactions
WHERE (((tblTransactions.TransactionType)="Membership"))
ORDER BY tblTransactions.MembershipExpiryDate DESC;

here is my query:

SELECT tblClients.ClientID, tblClients.FirstName, tblClients.LastName,
tblClients.Address1, tblClients.Address2, tblClients.Address3,
[tblClients].[City] & ", " & [tblClients]. [ProvinceStateAbbr] AS
CityProvince, tblClients.PhoneHome,
subqryClientsList.MembershipExpiryDate
FROM tblClients LEFT JOIN subqryClientsList ON tblClients.ClientID =
subqryClientsList.ClientID
ORDER BY tblClients.LastName;

Thanks,
George

"Les" <[email protected]> wrote in
message news: said:
I think this might work for you.
1) create query that selects membership transactions with
expiration dates from transaction table.
2) create new query, left join customers table to
membership query. (Change the join type to select all
from customers and only those from query that match)

-----Original Message-----
Hi, I'm really stuck with something and I'm hoping
someone here can
help.

I have one table with customers and and another with
customer
transactions. There are also two types of transactions;
product
purchases and membership purchases. If membership is
purchased there
is a expiry date save with the transaction record,
otherwise the
expiry date field is left empty. I have a client id field
joining the
two tables.

I'm trying create a query that returns "all" customer
names and
address information from the customers table, and also
returns
membership expiry dates from the transaction table if one
exists for
the customer. If no membership expiry date exists for the
customer I
still want there information returned in the query
though, just with
no expiry date.

I've tried many ways but don't seem to be able to figure
it out.

Could anyone help?

Thank you,

George
.
Click to expand...
.
Click to expand...
.
Click to expand...
 
Back
Top