Query Help!

[Golfinray]

I have a bit of a nightmare that I'm not quite sure how to deal with. The
boss has given me a spreadsheet that he had me import into Access to try and
pull some queries. It is a sheet that shows projects and payments for those
projects, so it looks like this:
Project 1 Payments Amount Cumulative
Payment 1 $10,000 $10,000
Payment 2 $10,000 $20,000
Payment 3 $20,000 $40,000
What I need to query is the project name, and some other data that goes with
project name which is easy to query. But then I need only the final
cumulative amount. I have tried max and several other approaches but can't
seem to get it. Help! Thanks so much.

 

[Dale Fye]

Is your project column empty for the 2nd, 3rd, ... entries for each project,
or is that field filled in properly? I would think you could do either:

SELECT Project, MAX(val(Cumulative)) as ProjectTotal
FROM yourTable
GROUP BY Project

or

SELECT Project, SUM(Amount) as ProjectTotal
FROM yourTable
GROUP BY Project.

Once you have either of these queries working, then link them to the other
table/worksheet that has the project specific information. Although neither
of these is going to work if you actually have blanks in the project column
under each of the projects (that correspond to the 2nd, 3rd, ... payments for
a particular project).

If that is the case, I would go back to Excel and fill that column back in.
I don't remember how to do this, but there is a way that involves selecting
the column, and then a couple of other things to automatically fill in all
blank values with the value in the previously filled in cell. I'm sure you
could write a macro to do it, but there is a built-in method in Excel to do
this.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.