VB help to make queries

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a database that has multiple "offices". Each "office" will have data
for multiple "years". Within each "year" there will be an "orginal estimated
completion date (OECD)". I want to find the greatest "OECD" for each "year"
and each "office".

I have created a series of queries which will return the correct
information, but I have hard-coded the values for "office" and "year"
(through 2012) in the queries.

I am looking for a VB way to do what I am doing through queries, because I
receive a message from Access stating it can't open anymore databases.

What I have so as queries:

This groups all the "OECD" into once Query for me.
SELECT [SAR Questions].FY, [SAR DATA].Office, [SAR DATA].[Original Estimated
Completion Date]
FROM [SAR Questions] INNER JOIN [SAR DATA] ON [SAR Questions].IEN = [SAR
DATA].[SAR Question]
GROUP BY [SAR Questions].FY, [SAR DATA].Office, [SAR DATA].[Original
Estimated Completion Date]
HAVING ((([SAR DATA].Office)="JMIS, Programs/Budget"));

This gives me the greatest "OECD for each year.
SELECT TOP 1 [SA Initial Completion Date Collection (Budget)].FY, [SA
Initial Completion Date Collection (Budget)].Office, [SA Initial Completion
Date Collection (Budget)].[Original Estimated Completion Date]
FROM [SA Initial Completion Date Collection (Budget)]
GROUP BY [SA Initial Completion Date Collection (Budget)].FY, [SA Initial
Completion Date Collection (Budget)].Office, [SA Initial Completion Date
Collection (Budget)].[Original Estimated Completion Date]
HAVING ((([SA Initial Completion Date Collection (Budget)].FY)=2006))
ORDER BY [SA Initial Completion Date Collection (Budget)].[Original
Estimated Completion Date] DESC;

Union
SELECT TOP 1 [SA Initial Completion Date Collection (Budget)].FY, [SA
Initial Completion Date Collection (Budget)].Office, [SA Initial Completion
Date Collection (Budget)].[Original Estimated Completion Date]
FROM [SA Initial Completion Date Collection (Budget)]
GROUP BY [SA Initial Completion Date Collection (Budget)].FY, [SA Initial
Completion Date Collection (Budget)].Office, [SA Initial Completion Date
Collection (Budget)].[Original Estimated Completion Date]
HAVING ((([SA Initial Completion Date Collection (Budget)].FY)=2007))
ORDER BY [SA Initial Completion Date Collection (Budget)].[Original
Estimated Completion Date] DESC;

....and so on for each year through 2012.

I have these two queries for each office, but when I try to join the second
query for all the offices together Access chokes.

My knoweldge of VB is about the same as a gnat, so dumb it down for me.

Thanks!!
 
Create a query that includes Offices, Years and OECD. Sort Offices ascending
and sort Years ascending. With the query in design view, click on the Sigma
button (looks like a capital E) in the menu at the top of the screen. Under
OECD, change GroupBy to Max. The query will return the greatest OECD for
each office for each year.

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





dsc2bjn said:
I have a database that has multiple "offices". Each "office" will have
data
for multiple "years". Within each "year" there will be an "orginal
estimated
completion date (OECD)". I want to find the greatest "OECD" for each
"year"
and each "office".

I have created a series of queries which will return the correct
information, but I have hard-coded the values for "office" and "year"
(through 2012) in the queries.

I am looking for a VB way to do what I am doing through queries, because I
receive a message from Access stating it can't open anymore databases.

What I have so as queries:

This groups all the "OECD" into once Query for me.
SELECT [SAR Questions].FY, [SAR DATA].Office, [SAR DATA].[Original
Estimated
Completion Date]
FROM [SAR Questions] INNER JOIN [SAR DATA] ON [SAR Questions].IEN = [SAR
DATA].[SAR Question]
GROUP BY [SAR Questions].FY, [SAR DATA].Office, [SAR DATA].[Original
Estimated Completion Date]
HAVING ((([SAR DATA].Office)="JMIS, Programs/Budget"));

This gives me the greatest "OECD for each year.
SELECT TOP 1 [SA Initial Completion Date Collection (Budget)].FY, [SA
Initial Completion Date Collection (Budget)].Office, [SA Initial
Completion
Date Collection (Budget)].[Original Estimated Completion Date]
FROM [SA Initial Completion Date Collection (Budget)]
GROUP BY [SA Initial Completion Date Collection (Budget)].FY, [SA Initial
Completion Date Collection (Budget)].Office, [SA Initial Completion Date
Collection (Budget)].[Original Estimated Completion Date]
HAVING ((([SA Initial Completion Date Collection (Budget)].FY)=2006))
ORDER BY [SA Initial Completion Date Collection (Budget)].[Original
Estimated Completion Date] DESC;

Union
SELECT TOP 1 [SA Initial Completion Date Collection (Budget)].FY, [SA
Initial Completion Date Collection (Budget)].Office, [SA Initial
Completion
Date Collection (Budget)].[Original Estimated Completion Date]
FROM [SA Initial Completion Date Collection (Budget)]
GROUP BY [SA Initial Completion Date Collection (Budget)].FY, [SA Initial
Completion Date Collection (Budget)].Office, [SA Initial Completion Date
Collection (Budget)].[Original Estimated Completion Date]
HAVING ((([SA Initial Completion Date Collection (Budget)].FY)=2007))
ORDER BY [SA Initial Completion Date Collection (Budget)].[Original
Estimated Completion Date] DESC;

...and so on for each year through 2012.

I have these two queries for each office, but when I try to join the
second
query for all the offices together Access chokes.

My knoweldge of VB is about the same as a gnat, so dumb it down for me.

Thanks!!
Click to expand...
 
Thanks!!!

I knew there had to be a more efficient way to do it. It never occurred to
me to use MAX.

Steve said:
Create a query that includes Offices, Years and OECD. Sort Offices ascending
and sort Years ascending. With the query in design view, click on the Sigma
button (looks like a capital E) in the menu at the top of the screen. Under
OECD, change GroupBy to Max. The query will return the greatest OECD for
each office for each year.

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





dsc2bjn said:
I have a database that has multiple "offices". Each "office" will have
data
for multiple "years". Within each "year" there will be an "orginal
estimated
completion date (OECD)". I want to find the greatest "OECD" for each
"year"
and each "office".

I have created a series of queries which will return the correct
information, but I have hard-coded the values for "office" and "year"
(through 2012) in the queries.

I am looking for a VB way to do what I am doing through queries, because I
receive a message from Access stating it can't open anymore databases.

What I have so as queries:

This groups all the "OECD" into once Query for me.
SELECT [SAR Questions].FY, [SAR DATA].Office, [SAR DATA].[Original
Estimated
Completion Date]
FROM [SAR Questions] INNER JOIN [SAR DATA] ON [SAR Questions].IEN = [SAR
DATA].[SAR Question]
GROUP BY [SAR Questions].FY, [SAR DATA].Office, [SAR DATA].[Original
Estimated Completion Date]
HAVING ((([SAR DATA].Office)="JMIS, Programs/Budget"));

This gives me the greatest "OECD for each year.
SELECT TOP 1 [SA Initial Completion Date Collection (Budget)].FY, [SA
Initial Completion Date Collection (Budget)].Office, [SA Initial
Completion
Date Collection (Budget)].[Original Estimated Completion Date]
FROM [SA Initial Completion Date Collection (Budget)]
GROUP BY [SA Initial Completion Date Collection (Budget)].FY, [SA Initial
Completion Date Collection (Budget)].Office, [SA Initial Completion Date
Collection (Budget)].[Original Estimated Completion Date]
HAVING ((([SA Initial Completion Date Collection (Budget)].FY)=2006))
ORDER BY [SA Initial Completion Date Collection (Budget)].[Original
Estimated Completion Date] DESC;

Union
SELECT TOP 1 [SA Initial Completion Date Collection (Budget)].FY, [SA
Initial Completion Date Collection (Budget)].Office, [SA Initial
Completion
Date Collection (Budget)].[Original Estimated Completion Date]
FROM [SA Initial Completion Date Collection (Budget)]
GROUP BY [SA Initial Completion Date Collection (Budget)].FY, [SA Initial
Completion Date Collection (Budget)].Office, [SA Initial Completion Date
Collection (Budget)].[Original Estimated Completion Date]
HAVING ((([SA Initial Completion Date Collection (Budget)].FY)=2007))
ORDER BY [SA Initial Completion Date Collection (Budget)].[Original
Estimated Completion Date] DESC;

...and so on for each year through 2012.

I have these two queries for each office, but when I try to join the
second
query for all the offices together Access chokes.

My knoweldge of VB is about the same as a gnat, so dumb it down for me.

Thanks!!
Click to expand...
Click to expand...
 
Steve said:
Create a query that includes Offices, Years and OECD. Sort Offices ascending
and sort Years ascending. With the query in design view, click on the Sigma
button (looks like a capital E) in the menu at the top of the screen. Under
OECD, change GroupBy to Max. The query will return the greatest OECD for
each office for each year.

PC Datasheet
Click to expand...

--
==>Let's ask it the 'nice' way...
Would you please, please go away Steve ??

This is to inform 'newbees' here about PCD' Steve:
http://home.tiscali.nl/arracom/whoissteve.html (updated, mainly the 'abuse-reporting' page...)
Until now 3700+ pageloads, 2375+ first-time visitors
(these figures are real and rapidly increasing)

Why is this ???
Because Steve is the ONLY person here who continues to advertise in the groups.

It is not relevant whether he advertised in *this* particular post or not...
==> We want him to know that these groups are *not* his private hunting grounds!

For those who don't like too see all these messages:
==> Simply killfile 'StopThisAdvertising'.
Newbees will still see this warning-message.

ArnoR
 
Although I agree with your request in general, I can not say I really care if
he advertises or not.

He did offer a solution and it was free advise.

I have seen other people put their business name or include a link to their
own private solutions web site in hundreds of posts.

As long as it is short and sweet what harm is the advertisement? I can (and
do) choose to ignore them. As long as he doesn't say, "go to my page and pay
me", I don't really care.
 
In this case, steve did not step outside the lines, but he has ignored the
rules so many times that "StopThis Advertising" has decided to tag ANY of
steve's posts. In fact, steve has made about a half dozen blatant
advertisements to the MS newsgroups in the past 24 hours.

There is little tolerance for steve since he has stated that the only way to
get real help is to contact him. This is a direct insult to all the experts
who provide far better help than he does for free.

John... Visio MVP
 
dsc2bjn said:
Although I agree with your request in general, I can not say I really care if
he advertises or not.
Click to expand...

Well, we *do* care !! , but surely you are entitled to *your* opinion.
He did offer a solution and it was free advise.
Click to expand...

Yes he was playing 'nice' here in this thread. I can see your point indeed.
In fact he had answered your question before I 'addressed' him, so I was thinking something like: "I will hear about this one".

It is because I *promised* Steve to hunt *each and every* of his posts, that I decided to post the usual answer also to this one.
It is a bit sad, but that's the way it has to be with Steve.
This is to show him that we are serious about this (IMO parents should treat their kids the same way...).
Well anyhow, Steve does *not* care...he does not give a shit about what we ask him....
The lunatic idiot thinks he is doing the *right thing* when he offers payed help.
http://groups.google.nl/group/micro...18755/8a15b3d0f82e7084?hl=nl#8a15b3d0f82e7084
Mind word wrap: This is the current thread 'reservation chart in access' in ms/public.access.tablesdesign
I have seen other people put their business name or include a link to their
own private solutions web site in hundreds of posts.
Click to expand...

That would be no problem. As I explained, this is NOT about the sigline...
As long as it is short and sweet what harm is the advertisement? I can (and
do) choose to ignore them. As long as he doesn't say, "go to my page and pay
me", I don't really care.
Click to expand...

IMO the groups would be completely lost if we would allow advertising.
If you look around at the ms.public.access groups and cdma you will see that Steve simply ignores every rule here.
And he has been doing that for years and years !!
Btw: Did you read this ?? http://home.tiscali.nl/arracom/whoissteve.html

Arno R
 
Back
Top