changing existing db from overall to monthly records (1 Viewer)

I'm altering a database to have certain fields be recorded monthly.

For example: instead of a client's file having "Total X Purchased" it would now be "Total X Purchased - Jan", "Total X Purchaed - Feb".

I want to spin the monthlies out of the master clients table & in to ex. tbl_clientsJan, tbl_clientsFeb, etc. but still have them linked; & have a "Totals" table that aggregates data from all of the tables (adds them up).

What's the best way to set this up?

I have an existing form, & I was thinking of just creating tabs for the months & subforms in each with their sources as the month tables, & removing the fields that don't change month-to-month (e.g. client name), with relationships between them. I would start by copying all of the master table data in to the month tables & allowing edits from there.

EDIT: the company doesn't track when the transactions occurred; I'm unable to group them along those lines.

I'm new to Access & don't want to make a mistake. If anyone has any advice, or if there's a safer method to do what I want to, I would really appreciate it.

What's the best way to set this up?

Click to expand...

To not do it in the manner you are suggesting. Once you have your properly structured tables, you can use queries to produce different views of that data. It sounds like you just want an aggregate query that has data by month.

Check out this link for some help on those types of queries: http://www.w3schools.com/sql/sql_groupby.asp

Thank you; but the way this business works, there is no "order date", & no way to group the numbers that way. Only "Totals" are tracked for each month & there's no data re: when the transactions occurred. I'll edit this info. in to my OP.

Do you have other suggestions for me?

I suggest you reread that page, focus on applying the concept to your situation and not the details. That page used those fields as examples to show how to use aggregate queries--which is what you need for this issue.

Can you post the current structure of your database along with some sample data? Include table and field names.

I created a sample database based on mine (as a lot of the data is sensitive).

I have 3 tables, all linked via ID (tbl_clients is AutoNumber, others are Number):

tbl_clients
last_name, first_name, title (a look-up)

tbl_clientsJan
x_widg_buy, x_widg_col (a multi-select look-up), y_widg_buy, y_widg_col (a multi-select look-up), month_memo (a memo field)

tbl_clientsFeb
(same as tbl_clientsJan)

& then there's frm_clients, with a client info. page & two tabs (Jan & Feb) with subforms for tbl_clientsJan & tbl_clientsFeb respectively.

I looked again at your link & I think I see what you're saying; if the fields that I'm updating all have the same name, I should just be able to pull them with a query & enter the data that way; but I'm not altogether sure how to do that & have the data go to the right month's table.

Thank you for your continued help; it really means a lot to me.

When building tables you shouldn't store relevant data in table or field names. You shouldn't have a table for January, February, etc.; you should have a field for that data. So that get's your structure to this:

x_widg_buy, x_widg_col, y_widg_buy, y_widg_col, month_memo, month_data

Where month_data will now hold what month that record is for (I vote using numbers, January=1, February=2...December=12). That gets you from 12 tables to 1 and allows you to group on that month_data field to bring all like months' data together.

However, you made the same storing-data-in-a-name mistake at the table level. Instead of x_widg_buy / y_widg_buy and x_widg_col / y_widg_col you should create a new field to distinguish what type the record is for (x or y). So now the proper structure will look like this:

widg_type, widg_buy, widg_col, month_memo, month_data

Tables are meant to grow vertically (by adding more records/rows) and not horizontally (by adding new columns/fields). The added benefit are easily being able to group by Type when running queries and if you ever add a new type (z, w, v, etc) all you have to do is enter data, not restructure your tables and queries to accomodate this new type.

I suspect widg_buy and widg_col might fall into the same category as your above issues (if one is for credits and one is for debits). If you can provide some sample data from your tables I can confirm this.

Those were example fields; while reflective, the actual database is huge & is approaching the field limit & I don't have the time/resources to redesign it & the staff who created it is long gone.

There are existing client records that have confidential data that, from this point on, we're trying to track on a monthly basis instead of overall. These aren't all number fields; some are checkboxes, others are memos, etc.

I'm mostly looking for a way to shoehorn a solution in to the existing mess. I can speak to my boss about doing a redesign but that won't be now or for awhile. Am I able to use a query to select a month, & enter the data in the resulting window that then feeds in to fields in a month table?

Like: parameter query -> select a month -> tbl_selectedmonth -> (data)? It would at least eliminate my dependency on tabs & subforms.

I understand that this isn't the ideal design, & I do appreciate the wisdom; I just don't have a lot of leeway or time.

is approaching the field limit & I don't have the time/resources to redesign it

Click to expand...

I've seen this time and again. Often the only way a big problem gets addressed is to have it turn into a disaster. The good news is sooner or later problems like this always get there and receive the attention they need.

I don't know what hack to use to keep this on life support. My only advice is to make a copy and then trial and error on it.

I can't afford to be that fatalist about it.

Thanks for your advice anyway.

Even if you don't feel you can fix it, you shouldn't make it worse and that's what you seem to be doing. If you don't have an actual date, but do have a month and year, just assume the 1st. Then you can use a date field and do this properly with a second table that holds a row per month. You then use crosstab queries to pivot the data into columns.