Query

  • Thread starter Thread starter Andre Adams
  • Start date Start date
A

Andre Adams

Ok. Let me try this again, this time, I'm going to try to be as clear as
possible. I am not familiar with SQL queries. I just don't understand the
language. I know that every query within access uses them, I just haven't
been trained in creating them. I have a table. It comprises all of my trade
data from 2004-2008 with the below fields.

Branch - Prefix to account Number
Acct # - Account trade is allocated to
Account Name - The client that the trade is allocated to
Symbol - The symbol that we bot or sold for the transaction
Description1 - The Description of the Symbol
Cusip - The Cusip for the Symbol
SettleDate - The date the transaction settled.
Tran - Whether the transaction was a buy or sell
Shrs/Contr - The amount of Shares purchase or sold
Trade Price - The price the trade was executed for
Commission Amount - The commission generated on the trade
Bltr - The execution indicator for the trade
Rep No - The rep associated witht the trade
Clearing Charge - The charge given to us from our clearing firm on the trade

Now. Given this information from 2004-2008, how would I pull (within the
same query) information relating commission for 3 date ranged periods. For
instance..I want to be able to pull:

1. Month to date total for 2008
2. Quarter to date total for 2008
3. Year to date total for 2008

Within this query, I want to be able to pull certain account numbers. I
have about 15 that I would put into the system to pull and nothing else.
I've tried to create queries for each of them (the 3 outlined above) and it
works fine for them individually. However, when I put them all in one query,
it omits certain lines and the totals are incorrect. Is there a way to do
this in access or do I need to look for a more manual way to produce this
information?
 
IF you already have three queries that work
AND the three queries return the same columns (different names are acceptable)
in the same order
THEN you can use a union query to combine the three queries into one query.

I would probably add one additional column to the three queries and name it
PeriodType. Then I would have values of M, Q, and Y for this column so I
could order the information by Period Type.

You can often get more specific help by posting the SQL view of your
query/queries.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
 
Thanks for your post John. I have over 150k records that I would have to
code as such. I can do the union query but, what am I searching by? I'd
have to change every single record in order to come up with that solution.
Is there another way?
 
POST the three queries that you have that work.

NO you would not make changes to every record.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
 
Month to date

SELECT qryIllinoisPlanSponsorMonthtoDate.[Account Name],
tblIllinoisPlanSponsor2.[Account #],
qryIllinoisPlanSponsorMonthtoDate.[SumOfCommission Amount],
tblIllinoisPlanSponsor2.[Short Name]
FROM qryIllinoisPlanSponsorMonthtoDate INNER JOIN tblIllinoisPlanSponsor2 ON
qryIllinoisPlanSponsorMonthtoDate.[Acct #] = tblIllinoisPlanSponsor2.[Account
#]
GROUP BY qryIllinoisPlanSponsorMonthtoDate.[Account Name],
tblIllinoisPlanSponsor2.[Account #],
qryIllinoisPlanSponsorMonthtoDate.[SumOfCommission Amount],
tblIllinoisPlanSponsor2.[Short Name]
HAVING (((tblIllinoisPlanSponsor2.[Short Name]) Like "*CHICAGO TEACHERS*"));

Quarter to date

SELECT tblIllinoisPlanSponsors.[Short Name],
tblIllinoisPlanSponsors.[Account #], qryIllinoisPlanSponsorQTR.[Account
Name], qryIllinoisPlanSponsorQTR.[SumOfShrs/Contr]
FROM tblIllinoisPlanSponsors INNER JOIN qryIllinoisPlanSponsorQTR ON
tblIllinoisPlanSponsors.[Account #] = qryIllinoisPlanSponsorQTR.[Acct #]
GROUP BY tblIllinoisPlanSponsors.[Short Name],
tblIllinoisPlanSponsors.[Account #], qryIllinoisPlanSponsorQTR.[Account
Name], qryIllinoisPlanSponsorQTR.[SumOfShrs/Contr]
HAVING (((tblIllinoisPlanSponsors.[Short Name]) Like "*CHICAGO TEACHERS*"));

YEAR TO DATE

SELECT qryIllinoisPlanSponsor.[Account Name],
tblIllinoisPlanSponsor2.[Account #], qryIllinoisPlanSponsor.[SumOfCommission
Amount]
FROM tblIllinoisPlanSponsor2 INNER JOIN qryIllinoisPlanSponsor ON
tblIllinoisPlanSponsor2.[Account #] = qryIllinoisPlanSponsor.[Acct #]
GROUP BY qryIllinoisPlanSponsor.[Account Name],
tblIllinoisPlanSponsor2.[Account #], qryIllinoisPlanSponsor.[SumOfCommission
Amount], tblIllinoisPlanSponsor2.[Short Name]
HAVING (((tblIllinoisPlanSponsor2.[Short Name]) Like "*CHICAGO TEACHERS*"));
 
Month to date
Change the queries as follows so they are parallel in construction.
SELECT "M" as PeriodType
, qryIllinoisPlanSponsorMonthtoDate.[Account Name],
tblIllinoisPlanSponsor2.[Account #],
qryIllinoisPlanSponsorMonthtoDate.[SumOfCommission Amount],
tblIllinoisPlanSponsor2.[Short Name]
FROM qryIllinoisPlanSponsorMonthtoDate INNER JOIN tblIllinoisPlanSponsor2 ON
qryIllinoisPlanSponsorMonthtoDate.[Acct #] =
tblIllinoisPlanSponsor2.[Account #]
GROUP BY "M", qryIllinoisPlanSponsorMonthtoDate.[Account Name],
tblIllinoisPlanSponsor2.[Account #],
qryIllinoisPlanSponsorMonthtoDate.[SumOfCommission Amount],
tblIllinoisPlanSponsor2.[Short Name]
HAVING (((tblIllinoisPlanSponsor2.[Short Name]) Like "*CHICAGO TEACHERS*"));

Quarter to date

SELECT "Q" as PeriodType
, qryIllinoisPlanSponsorQTR.[Account Name]
, tblIllinoisPlanSponsors.[Account #]
, qryIllinoisPlanSponsorQTR.[SumOfShrs/Contr]
, tblIllinoisPlanSponsors.[Short Name]
FROM tblIllinoisPlanSponsors INNER JOIN qryIllinoisPlanSponsorQTR ON
tblIllinoisPlanSponsors.[Account #] = qryIllinoisPlanSponsorQTR.[Acct #]
GROUP BY "Q", tblIllinoisPlanSponsors.[Short Name]
, tblIllinoisPlanSponsors.[Account #]
, qryIllinoisPlanSponsorQTR.[Account Name]
, qryIllinoisPlanSponsorQTR.[SumOfShrs/Contr]
HAVING (((tblIllinoisPlanSponsors.[Short Name]) Like "*CHICAGO TEACHERS*"));

YEAR TO DATE

SELECT "Y" as PeriodType,
qryIllinoisPlanSponsor.[Account Name]
, tblIllinoisPlanSponsor2.[Account #]
, qryIllinoisPlanSponsor.[SumOfCommission Amount]
, Null as ShortName
FROM tblIllinoisPlanSponsor2 INNER JOIN qryIllinoisPlanSponsor ON
tblIllinoisPlanSponsor2.[Account #] = qryIllinoisPlanSponsor.[Acct #]
GROUP BY "Y", qryIllinoisPlanSponsor.[Account Name],
tblIllinoisPlanSponsor2.[Account #], qryIllinoisPlanSponsor.[SumOfCommission
Amount], tblIllinoisPlanSponsor2.[Short Name]
HAVING (((tblIllinoisPlanSponsor2.[Short Name]) Like "*CHICAGO TEACHERS*"));


Now that they are parallel in construction, you can make a UNION query with
all the data in one query.

SELECT * FROM MonthQuery
UNION ALL
SELECT * FROM QuarterQuery
UNION ALL
SELECT * FROM AnnualQuery
ORDER BY [Account Number], [Account #], PeriodType

Another option would be to use the three queries as the source for three
sub-reports.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Andre said:
Month to date

SELECT qryIllinoisPlanSponsorMonthtoDate.[Account Name],
tblIllinoisPlanSponsor2.[Account #],
qryIllinoisPlanSponsorMonthtoDate.[SumOfCommission Amount],
tblIllinoisPlanSponsor2.[Short Name]
FROM qryIllinoisPlanSponsorMonthtoDate INNER JOIN tblIllinoisPlanSponsor2 ON
qryIllinoisPlanSponsorMonthtoDate.[Acct #] = tblIllinoisPlanSponsor2.[Account
#]
GROUP BY qryIllinoisPlanSponsorMonthtoDate.[Account Name],
tblIllinoisPlanSponsor2.[Account #],
qryIllinoisPlanSponsorMonthtoDate.[SumOfCommission Amount],
tblIllinoisPlanSponsor2.[Short Name]
HAVING (((tblIllinoisPlanSponsor2.[Short Name]) Like "*CHICAGO TEACHERS*"));

Quarter to date

SELECT tblIllinoisPlanSponsors.[Short Name],
tblIllinoisPlanSponsors.[Account #], qryIllinoisPlanSponsorQTR.[Account
Name], qryIllinoisPlanSponsorQTR.[SumOfShrs/Contr]
FROM tblIllinoisPlanSponsors INNER JOIN qryIllinoisPlanSponsorQTR ON
tblIllinoisPlanSponsors.[Account #] = qryIllinoisPlanSponsorQTR.[Acct #]
GROUP BY tblIllinoisPlanSponsors.[Short Name],
tblIllinoisPlanSponsors.[Account #], qryIllinoisPlanSponsorQTR.[Account
Name], qryIllinoisPlanSponsorQTR.[SumOfShrs/Contr]
HAVING (((tblIllinoisPlanSponsors.[Short Name]) Like "*CHICAGO TEACHERS*"));

YEAR TO DATE

SELECT qryIllinoisPlanSponsor.[Account Name],
tblIllinoisPlanSponsor2.[Account #], qryIllinoisPlanSponsor.[SumOfCommission
Amount]
FROM tblIllinoisPlanSponsor2 INNER JOIN qryIllinoisPlanSponsor ON
tblIllinoisPlanSponsor2.[Account #] = qryIllinoisPlanSponsor.[Acct #]
GROUP BY qryIllinoisPlanSponsor.[Account Name],
tblIllinoisPlanSponsor2.[Account #], qryIllinoisPlanSponsor.[SumOfCommission
Amount], tblIllinoisPlanSponsor2.[Short Name]
HAVING (((tblIllinoisPlanSponsor2.[Short Name]) Like "*CHICAGO TEACHERS*"));



John Spencer said:
POST the three queries that you have that work.

NO you would not make changes to every record.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
Click to expand...
Click to expand...
 
Ok. I've posted all of them. The only problem I have now is...where do I
put this Union Query? Do I create another query and post this in the SQL?

John Spencer said:
Month to date
Change the queries as follows so they are parallel in construction.
SELECT "M" as PeriodType
, qryIllinoisPlanSponsorMonthtoDate.[Account Name],
tblIllinoisPlanSponsor2.[Account #],
qryIllinoisPlanSponsorMonthtoDate.[SumOfCommission Amount],
tblIllinoisPlanSponsor2.[Short Name]
FROM qryIllinoisPlanSponsorMonthtoDate INNER JOIN tblIllinoisPlanSponsor2 ON
qryIllinoisPlanSponsorMonthtoDate.[Acct #] =
tblIllinoisPlanSponsor2.[Account #]
GROUP BY "M", qryIllinoisPlanSponsorMonthtoDate.[Account Name],
tblIllinoisPlanSponsor2.[Account #],
qryIllinoisPlanSponsorMonthtoDate.[SumOfCommission Amount],
tblIllinoisPlanSponsor2.[Short Name]
HAVING (((tblIllinoisPlanSponsor2.[Short Name]) Like "*CHICAGO TEACHERS*"));

Quarter to date

SELECT "Q" as PeriodType
, qryIllinoisPlanSponsorQTR.[Account Name]
, tblIllinoisPlanSponsors.[Account #]
, qryIllinoisPlanSponsorQTR.[SumOfShrs/Contr]
, tblIllinoisPlanSponsors.[Short Name]
FROM tblIllinoisPlanSponsors INNER JOIN qryIllinoisPlanSponsorQTR ON
tblIllinoisPlanSponsors.[Account #] = qryIllinoisPlanSponsorQTR.[Acct #]
GROUP BY "Q", tblIllinoisPlanSponsors.[Short Name]
, tblIllinoisPlanSponsors.[Account #]
, qryIllinoisPlanSponsorQTR.[Account Name]
, qryIllinoisPlanSponsorQTR.[SumOfShrs/Contr]
HAVING (((tblIllinoisPlanSponsors.[Short Name]) Like "*CHICAGO TEACHERS*"));

YEAR TO DATE

SELECT "Y" as PeriodType,
qryIllinoisPlanSponsor.[Account Name]
, tblIllinoisPlanSponsor2.[Account #]
, qryIllinoisPlanSponsor.[SumOfCommission Amount]
, Null as ShortName
FROM tblIllinoisPlanSponsor2 INNER JOIN qryIllinoisPlanSponsor ON
tblIllinoisPlanSponsor2.[Account #] = qryIllinoisPlanSponsor.[Acct #]
GROUP BY "Y", qryIllinoisPlanSponsor.[Account Name],
tblIllinoisPlanSponsor2.[Account #], qryIllinoisPlanSponsor.[SumOfCommission
Amount], tblIllinoisPlanSponsor2.[Short Name]
HAVING (((tblIllinoisPlanSponsor2.[Short Name]) Like "*CHICAGO TEACHERS*"));


Now that they are parallel in construction, you can make a UNION query with
all the data in one query.

SELECT * FROM MonthQuery
UNION ALL
SELECT * FROM QuarterQuery
UNION ALL
SELECT * FROM AnnualQuery
ORDER BY [Account Number], [Account #], PeriodType

Another option would be to use the three queries as the source for three
sub-reports.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Andre said:
Month to date

SELECT qryIllinoisPlanSponsorMonthtoDate.[Account Name],
tblIllinoisPlanSponsor2.[Account #],
qryIllinoisPlanSponsorMonthtoDate.[SumOfCommission Amount],
tblIllinoisPlanSponsor2.[Short Name]
FROM qryIllinoisPlanSponsorMonthtoDate INNER JOIN tblIllinoisPlanSponsor2 ON
qryIllinoisPlanSponsorMonthtoDate.[Acct #] = tblIllinoisPlanSponsor2.[Account
#]
GROUP BY qryIllinoisPlanSponsorMonthtoDate.[Account Name],
tblIllinoisPlanSponsor2.[Account #],
qryIllinoisPlanSponsorMonthtoDate.[SumOfCommission Amount],
tblIllinoisPlanSponsor2.[Short Name]
HAVING (((tblIllinoisPlanSponsor2.[Short Name]) Like "*CHICAGO TEACHERS*"));

Quarter to date

SELECT tblIllinoisPlanSponsors.[Short Name],
tblIllinoisPlanSponsors.[Account #], qryIllinoisPlanSponsorQTR.[Account
Name], qryIllinoisPlanSponsorQTR.[SumOfShrs/Contr]
FROM tblIllinoisPlanSponsors INNER JOIN qryIllinoisPlanSponsorQTR ON
tblIllinoisPlanSponsors.[Account #] = qryIllinoisPlanSponsorQTR.[Acct #]
GROUP BY tblIllinoisPlanSponsors.[Short Name],
tblIllinoisPlanSponsors.[Account #], qryIllinoisPlanSponsorQTR.[Account
Name], qryIllinoisPlanSponsorQTR.[SumOfShrs/Contr]
HAVING (((tblIllinoisPlanSponsors.[Short Name]) Like "*CHICAGO TEACHERS*"));

YEAR TO DATE

SELECT qryIllinoisPlanSponsor.[Account Name],
tblIllinoisPlanSponsor2.[Account #], qryIllinoisPlanSponsor.[SumOfCommission
Amount]
FROM tblIllinoisPlanSponsor2 INNER JOIN qryIllinoisPlanSponsor ON
tblIllinoisPlanSponsor2.[Account #] = qryIllinoisPlanSponsor.[Acct #]
GROUP BY qryIllinoisPlanSponsor.[Account Name],
tblIllinoisPlanSponsor2.[Account #], qryIllinoisPlanSponsor.[SumOfCommission
Amount], tblIllinoisPlanSponsor2.[Short Name]
HAVING (((tblIllinoisPlanSponsor2.[Short Name]) Like "*CHICAGO TEACHERS*"));



John Spencer said:
POST the three queries that you have that work.

NO you would not make changes to every record.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Andre Adams wrote:
Thanks for your post John. I have over 150k records that I would have to
code as such. I can do the union query but, what am I searching by? I'd
have to change every single record in order to come up with that solution.
Is there another way?

:

IF you already have three queries that work
AND the three queries return the same columns (different names are acceptable)
in the same order
THEN you can use a union query to combine the three queries into one query.

I would probably add one additional column to the three queries and name it
PeriodType. Then I would have values of M, Q, and Y for this column so I
could order the information by Period Type.

You can often get more specific help by posting the SQL view of your
query/queries.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Andre Adams wrote:
Ok. Let me try this again, this time, I'm going to try to be as clear as
possible. I am not familiar with SQL queries. I just don't understand the
language. I know that every query within access uses them, I just haven't
been trained in creating them. I have a table. It comprises all of my trade
data from 2004-2008 with the below fields.

Branch - Prefix to account Number
Acct # - Account trade is allocated to
Account Name - The client that the trade is allocated to
Symbol - The symbol that we bot or sold for the transaction
Description1 - The Description of the Symbol
Cusip - The Cusip for the Symbol
SettleDate - The date the transaction settled.
Tran - Whether the transaction was a buy or sell
Shrs/Contr - The amount of Shares purchase or sold
Trade Price - The price the trade was executed for
Commission Amount - The commission generated on the trade
Bltr - The execution indicator for the trade
Rep No - The rep associated witht the trade
Clearing Charge - The charge given to us from our clearing firm on the trade

Now. Given this information from 2004-2008, how would I pull (within the
same query) information relating commission for 3 date ranged periods. For
instance..I want to be able to pull:

1. Month to date total for 2008
2. Quarter to date total for 2008
3. Year to date total for 2008

Within this query, I want to be able to pull certain account numbers. I
have about 15 that I would put into the system to pull and nothing else.
I've tried to create queries for each of them (the 3 outlined above) and it
works fine for them individually. However, when I put them all in one query,
it omits certain lines and the totals are incorrect. Is there a way to do
this in access or do I need to look for a more manual way to produce this
information?
Click to expand...
Click to expand...
Click to expand...
 
I made a small error in the year to date, you need to group by the null
value also.

The union query is a NEW query. Open up a new query and switch to
design view. Then copy and paste the sample SQL statement into it.

UNION queries cannot be created in the query design view.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 
Hey John,

Everything worked like a charm. There is one thing though. Is there anyway
to get this to appear column based instead of row based? I can put all the
data in going down in one straight line, but, can I line it up according to
Manager and have all the commission and share totals read for each of them?
Pleaaassseee man....You've been a great help. I just need this last problem
fixed and I'm perfect!
 
You might look into using yet one more query.

A crosstab query might give you what you are looking for. I can't really give
you much more guidance on that since I don't see anything in your query
referring to a manager or a share total.

Just build the crosstab with the union query as the source.


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
 
Back
Top