I have a database where we log # of pallets, pieces and weight of inbound parts. We manually enter the pallets, pieces, and weight. I need a weekly sum of all three as well as a monthly sum. No big deal except that our billing cycle does not follow any standard calendar or fiscal year. We are beginning next year on 12/29/03 and the month end will be 01/25/04. 01/26/04 - 02/20/04 is the next month, 02/21/04 - 03/26/04 is the next month...etc. I am trying to avoid exporting this to Excel and doing all of this manually. Ideally I would like to have two tables, one for monthly and one for weekly, with the sums as I need to create charts based on the tables. I have racked my brain and can't come up with a solution. I know it can't be as difficlut as I am making it.
Thanks in adcance for any help,
javierryan6

I don't think you need the extra tables to hold sums becuase sums should always be a calculated value and never stored.

just create a form where the user can input a start date and an end date when you want to see the sums. that way they can see a weekly sum or a monthly sum.

Hi javierryan6. Welcome to Utter Access Forums.

My two cents' worth: DFish said it well, or another way: Try to not have stored sums in tables unless it is the absolute last resort. Having two queries that give you the desired results would be much preferable to stored sums in two tables.

(You may want to look at 353297 for some discussion re the idea.)

Also, I too once had a fractured calendar to work with, and it was a real bear to "program to", and I'm not sure that I remember the process we used to get around it, but if you'll post a short description of your tables that are involved maybe someone can come up with an answer for you that would support things like crosstab reports, which I presume would be useful for you.

G.

Sounds like you could just develop a query with the "between" function by entering your start bill cycle date and your end bill cycle date. I had a similar issue with an attendance occurrence database that I had developed for my department. Couldn't figure it out because the occurrences would drop off after a 13 week period, then I broke out my good old Excel skills and found that by entering the formula >=now()-91 in the date of the query, then I could have a rolling calendar.

Milleset,

Another two cents' worth:

Cent one: Using a fixed formula such as "... >=Now()-91..." works just fine, as long as the time periods involved are of uniform size, but when the time periods have "different lengths that vary randomly" and you need to handle more than one time period, then the formula (or the maintenance of it) gets real complex (with apologies to folks that like (-1)^0.5 logic) real fast.

Cent two: It tends to focus on a single time period, and I find contemplating coming up with a query that supports both fractured time periods and will work as a crosstab report base more than a bit scary.

The reason I say this is that my (one) experience with non-standard time periods soured me for life on the idea!

G.

Thanks for all of the responses. What I ended up doing was creating a table with four fields..
ID number (Autonumber)
ReferenceDescription(Text)
MinValue(Date)
MaxValue(Date)
And I used the "between" function to create a query that would attach the ID number and the Reference description to the sum for each field.
I hope that made sense.
My new problem is that I need to create charts for these sums. I need a chart that shows all of the months even if there are no values entered but when I run the query it does not keep the defined month if there are no values in that period. I need the chart to show all 12 predefined months even though there are no values entered yet. I am trying to avoid exporting to Excel and manually doing the charts but I am getting closer and closer. Any tips on charts is much appreciated.
Thanks

As I recall, we did pretty much the same thing (have a table that defined the time period start and ends) and then used that as a join to just about everything else in the application. Very Messy, but it "floated our boat", and that was the important thing.

Also, your problem in having the no-value months disappear from the results is a well-known problem (sometimes called the "Everybody not here please stand up and be counted" problem), and you may want to look at 353291 for one approach to the problem.

(Note: download the second of my two zipped A97 demo files -- the first one had an error.)

Also, the demo mdb in the referenced (corrected) database includes a cross-tab report that shows months with zero values, so you may want to see how the underlying data for it is created and stored.

HTH.

G.

I have worked on this for hours and can not get the results that I need. I know it can not be that difficult but I can not figure it out. I need some expert advice...I have attached my database. I have a query called MonthlySumQuery that gives me the sums that I need but I need it to show all of the RangeID and/or RangeDescription even if the table does not have anything in that date range. I need it so I can create charts that show all twelve date ranges. If I have to create a new query or query for each sum that I need that is ok I just need a solution. I know you guys can figure this out. Thanks again.

javierryan, If I am to understand this correct you need to query out the information that has "0" data, correct? If this is correct then find your common data between all the queries for your charts & set =0 as your criteria. I don't know if I am missing something here or not. Also, my next question would be are you trying to get 1 chart to display all 12 ranges? That is a little more complicated to get all 12 ranges into one chart. Can be done, but requires patience on your part. Good Luck!

Milleset

Hi Javieryyan,

You had already done all the heavy work, and all I did was convert your query to use a right join to pick up the missing rows. So, try the following:


SELECT DISTINCTROW MonthTable.RangeID, MonthTable.MinValue, MonthTable.MaxValue,
MonthTable.RangeDescription, Sum(AgileTable.PartPlt) AS [Sum Of PartPlt],
Sum(AgileTable.PartPcs) AS [Sum Of PartPcs], Sum(AgileTable.PartWgt) AS [Sum Of PartWgt],
Sum(AgileTable.BrazilPlt) AS [Sum Of BrazilPlt], Sum(AgileTable.BrazilPcs) AS [Sum Of BrazilPcs],
Sum(AgileTable.BrazilWgt) AS [Sum Of BrazilWgt]
FROM AgileTable RIGHT JOIN MonthTable ON
(AgileTable.Date Between [MonthTable].[MinValue] And [MonthTable].[MaxValue])
GROUP BY MonthTable.RangeID, MonthTable.MinValue,
MonthTable.MaxValue, MonthTable.RangeDescription;


HTH

G.

Hi Javieryyan,

I've been giving your problem a bit more thought, and have attached a zipped revision of your original A97 mdb, with the following changes included:

1. The [Date] column name was changed to [BolDate] to avoid possible confusion with the Date() function.

2. The RangeDescription was changed from Date/Time to Text (since that is what you are using it for and it works much better as a crosstab heading that way).

3. Added a [YearID] column to the MonthTable so as to be able to easily cluster the data by "non-standard-year-id".

4. Added a few new queries, the net result of which is a crosstab query in two forms.

I know that this is a bit more than your post mentioned, but it was easy to do and I thought that you might find the cross-tab query (one with a blank line of data, and one without the blank line of data) useful.

HTH.

You are the man Jinky44! Thank you so much. That is exactly what I needed.
javierryan

You are very welcome.

And thanks for the feedback. It is always nice to know that things worked out.

G.