Simple Join Queries

G

Guest

I cannot create join queries to save my life and it's crippled my ability to
get my data. For example, I have two tables: Trades and Equity

---Trades:
AccountNumber
Salesman
Commission
Date

----Equity
AccountNumber
Salesman
Date
Balance

The Trades table could have 100s of trades in an account in a day.
The Equity table has a balance for each account by day.
I want a query which has every transaction, but also gets the balance for
each account on that day. What relationships need to be created?
Everything I try creates duplicate records. Any good resources you know of?
 
K

Ken Snell \(MVP\)

Not 100% certain what you seek, but...

This type of query should give you the "total" of the commiissions for each
account number for each salesman on each day:

SELECT T.AccountNumber, T.Salesman, T.[Date],
Sum(T.Commission) AS TotalCommission
FROM Trades AS T
GROUP BY T.AccountNumber, T.Salesman, T.[Date];


I don't see how you would get information useful for the balance for an
account number from the Trades table, so I don't see why you'd join Trades
and Equity tables in any way?


Also, I note that you're using Date as the name of a control on a form. It
and many other words are reserved words in ACCESS and should not be used for
control names, field names, etc. See these Knowledge Base articles for more
information about reserved words and characters that should not be used:

List of reserved words in Access 2002 and Access 2003
http://support.microsoft.com/default.aspx?scid=kb;en-us;286335

List of Microsoft Jet 4.0 reserved words
http://support.microsoft.com/?id=321266

Special characters that you must avoid when you work with Access
databases
http://support.microsoft.com/?id=826763


See this site for code that allows you to validate your names as not being
VBA keywords:

basIsValidIdent - Validate Names to Make Sure They Aren't VBA Keywords
http://www.trigeminal.com/lang/1033/codes.asp?ItemID=18#18
 
K

Ken Snell \(MVP\)

Oh wait, OK, I see what you want.

Easiest way is to use a subquery instead of using a direct join.

SELECT T.AccountNumber, T.Salesman, T.[Date],
Sum(T.Commission) AS TotalCommission,
(SELECT TT.Balance FROM Equity AS TT
WHERE TT.AccountNumber = T.AccountNumber
AND TT.Salesman = T.Salesman AND
TT.[Date] = T.[Date]) AS AcccountBalance
FROM Trades AS T
GROUP BY T.AccountNumber, T.Salesman, T.[Date];


--

Ken Snell
<MS ACCESS MVP>



Ken Snell (MVP) said:
Not 100% certain what you seek, but...

This type of query should give you the "total" of the commiissions for
each account number for each salesman on each day:

SELECT T.AccountNumber, T.Salesman, T.[Date],
Sum(T.Commission) AS TotalCommission
FROM Trades AS T
GROUP BY T.AccountNumber, T.Salesman, T.[Date];


I don't see how you would get information useful for the balance for an
account number from the Trades table, so I don't see why you'd join Trades
and Equity tables in any way?


Also, I note that you're using Date as the name of a control on a form. It
and many other words are reserved words in ACCESS and should not be used
for
control names, field names, etc. See these Knowledge Base articles for
more
information about reserved words and characters that should not be used:

List of reserved words in Access 2002 and Access 2003
http://support.microsoft.com/default.aspx?scid=kb;en-us;286335

List of Microsoft Jet 4.0 reserved words
http://support.microsoft.com/?id=321266

Special characters that you must avoid when you work with Access
databases
http://support.microsoft.com/?id=826763


See this site for code that allows you to validate your names as not being
VBA keywords:

basIsValidIdent - Validate Names to Make Sure They Aren't VBA Keywords
http://www.trigeminal.com/lang/1033/codes.asp?ItemID=18#18

--

Ken Snell
<MS ACCESS MVP>




denton said:
I cannot create join queries to save my life and it's crippled my ability
to
get my data. For example, I have two tables: Trades and Equity

---Trades:
AccountNumber
Salesman
Commission
Date

----Equity
AccountNumber
Salesman
Date
Balance

The Trades table could have 100s of trades in an account in a day.
The Equity table has a balance for each account by day.
I want a query which has every transaction, but also gets the balance for
each account on that day. What relationships need to be created?
Everything I try creates duplicate records. Any good resources you know
of?
 
K

Ken Snell \(MVP\)

And, assuming that you want to show all transactions:

SELECT T.AccountNumber, T.Salesman, T.[Date],
T.Commission,
(SELECT TT.Balance FROM Equity AS TT
WHERE TT.AccountNumber = T.AccountNumber
AND TT.Salesman = T.Salesman AND
TT.[Date] = T.[Date]) AS AcccountBalance
FROM Trades AS T;


or, in this case, you can use direct joins:


SELECT T.AccountNumber, T.Salesman, T.[Date],
T.Commission, TT.Balance AS AcccountBalance
FROM Trades AS T INNER JOIN Equity AS TT
ON TT.AccountNumber = T.AccountNumber
AND TT.Salesman = T.Salesman AND
TT.[Date] = T.[Date];



--

Ken Snell
<MS ACCESS MVP>



Ken Snell (MVP) said:
Oh wait, OK, I see what you want.

Easiest way is to use a subquery instead of using a direct join.

SELECT T.AccountNumber, T.Salesman, T.[Date],
Sum(T.Commission) AS TotalCommission,
(SELECT TT.Balance FROM Equity AS TT
WHERE TT.AccountNumber = T.AccountNumber
AND TT.Salesman = T.Salesman AND
TT.[Date] = T.[Date]) AS AcccountBalance
FROM Trades AS T
GROUP BY T.AccountNumber, T.Salesman, T.[Date];


--

Ken Snell
<MS ACCESS MVP>



Ken Snell (MVP) said:
Not 100% certain what you seek, but...

This type of query should give you the "total" of the commiissions for
each account number for each salesman on each day:

SELECT T.AccountNumber, T.Salesman, T.[Date],
Sum(T.Commission) AS TotalCommission
FROM Trades AS T
GROUP BY T.AccountNumber, T.Salesman, T.[Date];


I don't see how you would get information useful for the balance for an
account number from the Trades table, so I don't see why you'd join
Trades and Equity tables in any way?


Also, I note that you're using Date as the name of a control on a form.
It
and many other words are reserved words in ACCESS and should not be used
for
control names, field names, etc. See these Knowledge Base articles for
more
information about reserved words and characters that should not be used:

List of reserved words in Access 2002 and Access 2003
http://support.microsoft.com/default.aspx?scid=kb;en-us;286335

List of Microsoft Jet 4.0 reserved words
http://support.microsoft.com/?id=321266

Special characters that you must avoid when you work with Access
databases
http://support.microsoft.com/?id=826763


See this site for code that allows you to validate your names as not
being
VBA keywords:

basIsValidIdent - Validate Names to Make Sure They Aren't VBA
Keywords
http://www.trigeminal.com/lang/1033/codes.asp?ItemID=18#18

--

Ken Snell
<MS ACCESS MVP>




denton said:
I cannot create join queries to save my life and it's crippled my ability
to
get my data. For example, I have two tables: Trades and Equity

---Trades:
AccountNumber
Salesman
Commission
Date

----Equity
AccountNumber
Salesman
Date
Balance

The Trades table could have 100s of trades in an account in a day.
The Equity table has a balance for each account by day.
I want a query which has every transaction, but also gets the balance
for
each account on that day. What relationships need to be created?
Everything I try creates duplicate records. Any good resources you know
of?
 
S

Steve

To start, you need the following tables:

TblSalesman
SalesmanID
FirstName
LastName

TblAccount
AccountID
AccountName

TblTrade
TradeID
AccountID
StockSymbol
TradeDate
SalesmanID
TradeAmount
Commission

To enter your data, you need a form/subform. The main form would be based on
TblAccount and the subform would be based on TblTrade. On the form you would
have a combobox based on TblSalesman to select the salesman for the trade.
You could have a search function on the form to lookup a specific account.

I am not sure what you mean by balance. You could have another form that
displays accounts and the balance of each account. Whatever your meaning of
Balance, Balance should be calculated from the data in TblTrade and not be
something you enter.

Need help setting this up? I can help you for a very reasonable fee.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)
 
G

Guest

Thanks so much. It works

Ken Snell (MVP) said:
And, assuming that you want to show all transactions:

SELECT T.AccountNumber, T.Salesman, T.[Date],
T.Commission,
(SELECT TT.Balance FROM Equity AS TT
WHERE TT.AccountNumber = T.AccountNumber
AND TT.Salesman = T.Salesman AND
TT.[Date] = T.[Date]) AS AcccountBalance
FROM Trades AS T;


or, in this case, you can use direct joins:


SELECT T.AccountNumber, T.Salesman, T.[Date],
T.Commission, TT.Balance AS AcccountBalance
FROM Trades AS T INNER JOIN Equity AS TT
ON TT.AccountNumber = T.AccountNumber
AND TT.Salesman = T.Salesman AND
TT.[Date] = T.[Date];



--

Ken Snell
<MS ACCESS MVP>



Ken Snell (MVP) said:
Oh wait, OK, I see what you want.

Easiest way is to use a subquery instead of using a direct join.

SELECT T.AccountNumber, T.Salesman, T.[Date],
Sum(T.Commission) AS TotalCommission,
(SELECT TT.Balance FROM Equity AS TT
WHERE TT.AccountNumber = T.AccountNumber
AND TT.Salesman = T.Salesman AND
TT.[Date] = T.[Date]) AS AcccountBalance
FROM Trades AS T
GROUP BY T.AccountNumber, T.Salesman, T.[Date];


--

Ken Snell
<MS ACCESS MVP>



Ken Snell (MVP) said:
Not 100% certain what you seek, but...

This type of query should give you the "total" of the commiissions for
each account number for each salesman on each day:

SELECT T.AccountNumber, T.Salesman, T.[Date],
Sum(T.Commission) AS TotalCommission
FROM Trades AS T
GROUP BY T.AccountNumber, T.Salesman, T.[Date];


I don't see how you would get information useful for the balance for an
account number from the Trades table, so I don't see why you'd join
Trades and Equity tables in any way?


Also, I note that you're using Date as the name of a control on a form.
It
and many other words are reserved words in ACCESS and should not be used
for
control names, field names, etc. See these Knowledge Base articles for
more
information about reserved words and characters that should not be used:

List of reserved words in Access 2002 and Access 2003
http://support.microsoft.com/default.aspx?scid=kb;en-us;286335

List of Microsoft Jet 4.0 reserved words
http://support.microsoft.com/?id=321266

Special characters that you must avoid when you work with Access
databases
http://support.microsoft.com/?id=826763


See this site for code that allows you to validate your names as not
being
VBA keywords:

basIsValidIdent - Validate Names to Make Sure They Aren't VBA
Keywords
http://www.trigeminal.com/lang/1033/codes.asp?ItemID=18#18

--

Ken Snell
<MS ACCESS MVP>




I cannot create join queries to save my life and it's crippled my ability
to
get my data. For example, I have two tables: Trades and Equity

---Trades:
AccountNumber
Salesman
Commission
Date

----Equity
AccountNumber
Salesman
Date
Balance

The Trades table could have 100s of trades in an account in a day.
The Equity table has a balance for each account by day.
I want a query which has every transaction, but also gets the balance
for
each account on that day. What relationships need to be created?
Everything I try creates duplicate records. Any good resources you know
of?
 
K

Ken Snell \(MVP\)

Steve said:
Need help setting this up? I can help you for a very reasonable fee.


Sorry Steve... we provide assistance to posters for free here....
 
J

John Marshall, MVP

You never learn. These newsgroups are for FREE help with Microsoft products,
not a feeding ground for con artists.

So the "sorry" in your sigline has nothing to do with trying to repent for
your past behaviour?

John... Visio MVP
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top