Re: Query

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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 Adams wrote:
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" wrote:

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?

"John Spencer" wrote:

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

Relevant Pages

  • Re: TempDB Not automatically growing
    ... Do you have it set to grow by a large amount? ... query must wait for the database to finish growing. ... Allocating disc space ... > it fills up my users queries don't work. ...
    (microsoft.public.sqlserver.server)
  • Re: Bottleneck?, Insert and select on the same table.....
    ... Considering this amount of data, are there any condition that could ... That all heavily depends on the nature of the data and queries. ... If I am not mistaken you have 70,560 bytes / sec raw data which does not sound too dramatic. ... For the read side we have no information about the nature of the data and the queries and how often they are executed etc. ...
    (comp.databases.oracle.server)
  • Re: Performance problem in sql ce
    ... It would be more work since you'd need to do all the joins programmatically, but you could probably get very good performance going that route, even with the amount of data you have. ... > I am very disappointed with the sql queries performance in sql ce. ... Device Application Development MVP ...
    (microsoft.public.sqlserver.ce)
  • RE: DS_NONPDQ_QUERY_MEM
    ... for those types of queries ... should I then allocate more memory to PDQ so I can allocate ... Jim Goldrick ... so before DS_NONPDQ_QUERY_MEM only a small fixed amount of memory ...
    (comp.databases.informix)