Programmer brainfart

[kousotsu]

I know I shouldn't have to ask this question, but I've obviously blotted this from my head somehow :

I have a formatted date imported from Excel (Longest date possible: dd/mmmm/yyyy, hh:mm:sss - formatted likewise) upon which I need to operate solely by month. I asked to be able to strip the rest of the irrelevant temporal data, but was denied that option.

What I need to build is a report where Point totals are calculated for each Area and Vendor by Month, summing at the bottom of the report for YTD. I currently have a report that does this for Vendor and Area separately, but only totals for each. How do I operate on this data to pull the Month into the report?

-=--=--=-
FORMAT OF TABLES:
-tblSubstation-
Substation [PK]
Area (seven areas, need a total for each area)
currentVendor[FK - tblVendor]
...
-tblOutage-
eventNumber [PK]
feederNumber [FK - tblFeeder]
beginTime (**this is the field where I'm having issues**)
...
custInterrupt (number of customers interrupted)
Points (dependant on custInterrupt, already populated)
-tblFeeder-
feederNumber [PK]
Substation [FK]
...
-tblVendor-
Vendor [PK]
...
-=--=--=-

Sorry to be so stupid - I know the answer has to be something simple I'm overlooking.

Thanks, in advance!

 

[dcx693]

You can create a totals query where you calculate the totals by Vendor, by Area, and then finally by date. If you're not sure about what a "totals" query is, check out the Access on-line help about the topic and let us know if you need more help.

Now, about how to handle the date info. In your query, you can create a calculated field that returns to you exactly what you need: the month and year of the date. Something like this should work: Format([beginTime],"yyyy/mm"). That expression will give you the four-digit year and two-digit month in a text string that you can sort and group on. Just be aware that you shouldn't try to do any date calculations based on that text string.

 

[Pat Hartman]

A totals query can only produce one level of summary at a time. If you need multiple levels, it will be easier to use a report. As long as the date field is defined as a date data type (the formatting is irrelevant), the report's sorting and grouping options will group by month.

 

[kousotsu]

the report's sorting and grouping options will group by month.

Yes, they do, but then they refuse to Sum by "Points", but rather continue to separate them by the irrelevant parts of "beginTime." I got a crosstab query/by month report with dcx's suggestion that seems to work, but I will need to rebuild the query/report as each month goes by. Here's what I need to see:

-=--=--=-
-Points By Area/Vendor/Month Report-
Area...............Jan.....Feb...-=>
.....Vendor1.....ppt.....ppt...-=>..Total
.....Vendor2.....ppt.....ppt...-=>..Total
.....Vendor3.....ppt.....ppt...-=>..Total
....................Total...Total...-=>Total
Area.....-=>
-=--=--=-

Again, maybe I'm just not seeing the obvious, just like the Format(<table/query>, <format>) bit. Am I missing a way to organize the report so that it functions the way I've outlined it above?


(EDIT: had to reformat the example... forum strips extra spaces.)

 

[dcx693]

Pat, as usual, is correct. I'm not the biggest report user, but sure enough it's much easier to do the summarizing using a report. Use the Sorting and Grouping function, when you get to your date field, set the Group On to Month.

 

[Pat Hartman]

Yes, they do, but then they refuse to Sum by "Points", but rather continue to separate them by the irrelevant parts of "beginTime."

- sorting and grouping are done hierarchially. So if Points is the most important, you would sort and group on Points first and the month part of date second.

 

[kousotsu]

Archimedes was right...

- sorting and grouping are done hierarchially. So if Points is the most important, you would sort and group on Points first and the month part of date second.

Eureka! ^_^... as usual, you guys |20xx0|2z my |30xx0|2z... Thanks again. God, how I wish I was just programming this in PHP like I originally wanted to. **sigh**

 

[Pat Hartman]

Sorting is sorting and it wouldn't be any different in PHP so you learned something from your Access project that you can apply anywhere

 

[kousotsu]

Minor Rant

Sorting is sorting and it wouldn't be any different in PHP so you learned something from your Access project that you can apply anywhere

While that's true, I think it's just a matter of my knowing that language better than I know VBA/Access. I know VB well enough to be dangerous to myself and others, but I'm thoroughly versed in web languages. That's really what I do, but work is work and has been pretty lean without certs or a degree. I have plenty of past deliverables, but sometimes that's not enough for people to see that you really DO know what you're doing. ::shrug:: But that's a rant for another section of the forum ^_^

Thanks again.