Fill in field info from previous record 2

[BoomerBroomer]

I have downloaded a report from Simply Accounting into Excel and subsequently into MS Access. The table in Access is as follows (formatted exactly as the report prints in Simply Acctg & I can't get a different format in the source report) (each row is a record):

ID PROJECT ACCOUNT AMT
1 Pour Basement 55-001 20.00
2 55-004 100.00
3 55-010 29.00
4 Erect Walls 55-001 400.00
5 55-010 1000.00

I want to summarize this into one record for each PROJECT , probably using a query grouping, but I need to fill in "Pour Basemet" for record 2 and 3, and "Erect Walls" for record 5. I've tried the Dlookup solution in a query but it only updates one record in each pass. My table consists of many blank fields with varying number of blanks for each PROJECT. I want this to be user friendly ie run from a Command Button, BUT MY PRIMARY CONCERN IS FILLING IN THE BLANK FIELDS WITH THE PROJECT NAME.

Any ideas or help would be appreciated!

 

[rdodge]

One way to do this is to create a query. The "SELECT" clause should be as:

SELECT PROJECT, SUM(AMT)

By doing this, the query will return one record per project with the sum value of AMT by using the aggregate SUM function on the AMT field.
[tt]
Pour Basement 149
Erect Walls 1400[/tt]

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
When the going gets tough, the tough gets going.

 

[PaulBricker]

It really depends on a few things. You should try this in a query first to see how the results look, then if they look right, you can update your table using an update query. Here's what you do.

In a module put this Function.

Function FillInBlanks(myVal As Variant) As String
If Not IsNull(myVal) Then
Static myStr As String
myStr = myVal
FillInBlanks = myStr
Else
FillInBlanks = myStr
End If
End Function

Now the catch is, the First Record in the query has to have a value in the Project Field. After that it doesn't matter as long as the format follows your current setup.
Then in a new column of your query put

MyProjects:FillInBlanks([PROJECT])

Take a look at the results. My results were good. Everything filled in properly. If it does for you also, then in the query, remove the calculated field MyProjects, change the query to an Update Query (on the menu bar go to Query...Update and on the UpdateTo line for you PROJECT field put
FillInBlanks([PROJECT])

Then run the Update query. It should fill in the blanks in your Table. Then you can create you Totals query from your table.

Paul

 

[PaulBricker]

WARNING, if you use a Function, you need to enclose the FieldName PROJECT in square brackets just like I have it in my post. If you just put
FillInBlanks(Project)
Access will put ""'s around the word Project and fill in all your Project fields with the word Project. Don't want that. You might want to make a backup of your original Table just as a precaution. I was just testing a larger recordset for any anomolies and ran into that problem.

Paul

 

[PaulBricker]

rdodge, I'm lost here someplace. I've got
Select PROJECT, Sum(Amount)
From testProject

and I get the error message that I tried to execute a query that does not include the the expression PROJECT as part of an aggregate function.
I'm curious how you use the Sum() function on one field in a query without grouping your PROJECT field and if you group the project field, then the nulls group as one of the groups.

Thanks for any clarification.

Paul

 

[rdodge]

Given the situation given above, I would use a macro to be ran immediately after it's refreshed (updated) in Excel to fill in the project names. May have to look in the Vendor's help files to see how to do this, like I use "Showcase Strategy Query" to get information from our Main DB program and can only take it into Excel, but then there's other processes that I need to run on that data before I can put it into a report format. Of course, all of the coding to automate the processes would be done from within Excel.

One such code to use in Excel would be:

Dim C as Range, ProjCol as Range, ProjName as String
Set ProjCol = ThisWorkbook.Worksheets("Sheet1&quot.Range("Project&quot
For Each C in ProjCol
If C.Value <> &quot;&quot; Then
ProjName = C.Value
Else
C.Value = ProjName
End If
Next

Also check within the linked column to see if it has a range name as the above code assumes that. If it does, then use that range name to select that particular column/range to run the code on. If the column does not have a range name, you may want to give it one manually. This should allow for the aggregate sum function of the query to work properly once this data is taken over to Access.

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
When the going gets tough, the tough gets going.