VB help to make queries

D

dsc2bjn

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

Steve

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

dsc2bjn

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

dsc2bjn

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

John Marshall, MVP

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
 

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

Need help with a VB script for a command button in Access 2003 1
Best way to calculate values that depend on historic values? 3
Remote Databasing - User Implementation and Incremental Numbering 3
Remote Databasing Problems - Design and Incremental Numbering 1
help with vbscript to average a report of dates 0
Macro to break one row of data into multiple rows, with calcuation 1
Advice on database structure to replace multiple spreadsheets 1
Query not updatable - advise on how to change it 4

Top