Table fields

[jsic1210]

Hello,
I am in need of some suggestions. I have a table where I track monthly revenue by customer. So my field names include January, February...December. I have a query that tracks the total for the year, the annualized amount (based on a parameter [As Of Date]), 3 months lagging revenue (again based on the parameter, so if the [As Of Date] is in June, there will be 3 columns: Month-1 Rev, Month-2 Rev, Month-3 Rev). I am trying to eliminate the need for parameters in the query, so that making a report is simpler, but I don't know what to do! I know that having columns named after months isn't the best thing to do, but I don't know another way. Does anyone have any suggestions?
Thanks!!!
Jeff

 

[plog]

Yes, structure you're data properly: https://en.wikipedia.org/wiki/Database_normalization.

Field names shouldn't hold specific information about the data. Nor should they be numerated (Month1, Month2, Month3...). Instead of a field for each month, you need one field to designate the date of the data: RevenueDate. Your table would look like this:

RevenueTable
RevenueDate, Revenue
1/1/2013, 500
2/1/2013, 726
3/1/2013, 419
...

You get your data like that and these queries become easy to generate. You can then Sum (http://www.techonthenet.com/access/functions/numeric/sum.php) your data and use Date Functions (http://www.techonthenet.com/access/functions/) to group them together.

 

[jsic1210]

That was one of my thoughts, but there is a big problem with how the data is retrieved. We have a monthly file (updated daily) tracking revenue in Excel form (With the columns: Customer ID, Customer Name, Usage Charge). I am trying to put a weekly report out, but it tracks the cumulative monthly charges. So whatever information I import next week, I need to REPLACE any old data (from that month).

Of course, typing it now, I think I have figured out a solution. When I import it, I will have a pop-up to enter the date of the info (say 7/16/13), it will automatically insert that date in the date field, but not before deleting any entries with the date in July of 2013. So I will give this a try.

Thanks for the push in the right direction!

 

[jsic1210]

Okay, I have follow-up question. I have created a table where the entries are separate for each month, and have therefore added a "Month End Date" field. I have a pop-up form that asks the user to enter the month end date. In the report, I want two side-by-side columns, one for the current month revenue, one for the previous month revenue. Is changing the control source of the fields (based on what's entered in the pop-up form) the only way to do this?

I hope this makes sense.

Thanks!

 

[plog]

Is changing the control source of the fields (based on what's entered in the pop-up form) the only way to do this?

I always get lost when people mention forms and reports and asks questions about tables and queries. So, no, it doesn't make complete sense. Can you post what you want your report to look like? I don't care about fonts and colors, I want to know about the specific data.

 

[jsic1210]

On the pop-up form, there's a text box where I enter [Month End Date], then click a command button to open the report. If I enter "6/30/13," the report should look like this:

Company Name        June Revenue       May Revenue
ABC Company               $6,000            $5,000

So the June revenue field would sum all revenue where [Month End Date] is 6/30/13 (in the query) and May Revenue where [Month End Date] is 5/31/13.

 

[plog]

The simple way is to do a DSUM (http://www.techonthenet.com/access/functions/domain/dsum.php), calculating the prior month end date and putting that in the criteria of the DSUM.

To make that easier, I would change [Month End Date] to [Month Begin Date]. It will make calculating the prior month's value so much easier--you will just use the DateAdd function (http://www.techonthenet.com/access/functions/date/dateadd.php). Trying to calculate the last day of the prior month isn't as simple. For example 2/28/2003 would return 1/28/2003.

 

[jsic1210]

I am running into a couple problems. One, I am using DCounts to rank each customer's revenue within a given month. Two, when using DSum, the query yields the correct number, but it's in text format. I know I can use the Format([Field],"Currency") function, but is there another way to fix this?

Thanks.

 

[plog]

Problem One: You didn't tell me what the problem was with your DCounts.

Problem Two: multiply the DSUM by 1
=DSUM("[FieldName]", "TableName")*1

 

[jsic1210]

I guess listing the problem would help. It's making the query run slow at times. It only takes a few seconds for the query to load, but if I try copying and pasting (just to see it in Excel), Access freezes.

And thanks for the multiply by 1 tip!

 

[plog]

The DCount, and the DSum for that matter, are expensive operations--they have to look at the dataset for every record.

 

[highandwild]

There is another way that opens up some possibilities.

See attached database.

This method allows you to specify the data range and even show columns where no data exists. If you were showing results for this year then columns August to december will be there but with zero values.

It is very crude but demonstrates the technique.