Query

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?
 
J

John Spencer

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
 
A

Andre Adams

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?
 
J

John Spencer

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
 
A

Andre Adams

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*"));
 
J

John Spencer

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

Andre Adams

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

John Spencer

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
'====================================================
 
A

Andre Adams

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!
 
J

John Spencer

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
 

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

Similar Threads

Query. 2
Report 1
Report 3
New Database 3
...cannot use a calculated field in a Where clause... 2
New Query 7
Access 2003 Functionality 2
Help with: ...query that does not include specified expression ....as part of an aggregate function. 3

Top