Adding a field to existing table in access using vba

[rahulapatel]

Ok so I am working on a Access project and I have hit a roadblock and I need your expert help.

I have a table(tblWorkerStats) with fields: [Worker Code], [worker name], [Jan '16], [Feb '16], [MAr '16], [Apr '16]. All the month name fields contain a count of cases that the worker has not processed in time for that month. I have another table (tblOverdue) with fields: [worker code], [case number] which lists all the individual case numbers that were not processed in time for that current month. We get this monthly data from a remote server and only provides data for the current month.

So what I am trying to do is populate a new field in (tblWorkerStats) each month to store the count of cases per worker for the current month. How would I go about doing that?
I do not think I can use a APPEND or UPDATE query. What I thought of doing is call on VBA event procedure on the form to store the data from (tblWorkerStats) into rs1 as recordset. And store results from (qryCountOverdue) into rs2 as recordset.

Now I want rs2 data appended to rs1 joined on [worker code]. Also if possible, I want the new field name to be the current month name.

And another complex problem is that on the report is that each month I only want to show stats for the last 3 months and since the field names of the last 3 months are going to change every month, I do not know how I would be able to accomplish that. Maybe display last 3 fields based on the field location ordinals but I do not know how.

P.S. I am very new to Access and VBA but I'm catching up very quick and with your help I'm hoping I'll learn a lot more.

Thank you

[Tony Hine]

If I understand your question correctly you have several repeating Fields:- [Jan '16], [Feb '16], [MAr '16], [Apr '16]...

I suspect this is the essence of your problem. This isn't the way you would do things in MS Access this is more like the way you would do them in Excel.

You inadvertently pointed out the problem yourself in your statement "And another complex problem is" The data in each of those Fields is of the same sort, therefore it should be stored in one field in a table with another field to identify the data. Once you grasp this, you will find things become much easier, you will use queries efficiently and effectively to get the results you need.

So to sum up a rather long answer, you need to change your database structure. The sooner you change it the better.

[rahulapatel]

Thank you for your response Tony.

I understand the structure is not the best, however we get data from a remote server for current month only. And the data we get is a list of cases every month and what I'm interested in is only storing the count of cases each month.

So what you are suggesting is I create a separate table with fields, worker code, count of cases and month and i'll identify the count with the month. That makes sense to me and I probably could accomplish that through a APPEND query every month and assign value to the month field with format(Now(),"mmmm\'yy"). Am I correct? Would I be able to use aggregates in a Append query since all I need to store is the count of cases per worker and not the whole list of cases.

Also,on the report, how would I show 3 separate columns for the last 3 months where data is coming from the same field? I am not able to think that part logically.

Thanks again.

[Tony Hine]

I still think you need to change your structure. You are getting the data in per month, count per user.

Let's say you're next month is [May '16]
You have 5 user --- ID's 1,2,3,4,5
Counts of --- 43,65,23,87,12
Instead of adding that data into a new field, which is something you should never really consider, then you add the data to fields in a table. The table structure would look like this:-

fldMonth --- fldUser --- fldCount
[May '16] --- 1 -------------43
[May '16] --- 2 -------------65
[May '16] --- 3 -------------23
[May '16] --- 4 -------------87
[May '16] --- 5 -------------12

[Tony Hine]

As to the report, how about three subreports which display each month next to each other?

The SQL for each subreport could be written so that it would automatically pick up the data for the last 3 months.

[rahulapatel]

Thanks again Tony. I said exactly what you are suggesting but I guess I didn't word my explanation correctly.
I modified my current structure and split my current table into 2 tables.
tblWorkers
[Worker Code] [Worker name] [location]
a1----------- john doe----- california
a2----------- jane doe----- utah
b1----------- john smith--- texas

tblStats
[worker code] [overdues] [month]
a1------------25-------- apr
a1------------15-------- may
a1------------27-------- mar
a2------------52-------- may
a2------------36-------- apr
a2------------12-------- mar

Now what my logic tells me is when I run the countcases query each month, I should make that a append query that appends to tblStats and the value of the month field would be Format(now(),"mmm"). Is my logic correct so far?
If it is correct, I still cant logically think I would I get my report to look like
[Worker Code] [worker name] [mar] [apr] [may]
a1------------John Doe-------27----25----15--
a2------------Jane Doe-------12----36----52--

And next month I dont want to redesign the report but next month report should show
[Worker Code] [worker name] [apr] [may] [jun]
a1------------John Doe-------25----15----xx--
a2------------Jane Doe-------36----52----xx--

I hope I was more clear now. Thank you again for sparing the time to help me out. I really appreciate it.

[Tony Hine]

[Worker Code] [worker name] [apr] [may] [jun]
a1------------John Doe-------25----15----xx--
a2------------Jane Doe-------36----52----xx--

Why is the worker code different?

Opps --- I see i read Jane as "John"

[rahulapatel]

Also, going by the above flow, I might face another problem with the APPEND query. I do not want the query to execute if it has already been executed for the current month. i.e. if there is [month] field already has Format(now(),"mmm") value then abort query. I think I might need to use VBA to achieve that. Any ideas?

[Tony Hine]

YouTube Video - MS Access Building a Cross Tab Query

[zmbd]

1) IMHO: You need to take a look at, Database Normalization and Table Structures

Your tblWorkers might be able to be taken apart one more level depending on the actual usage for location.
-- Can a Worker be in more than one location?
-- COULD the Worker EVER be in multiple locations in the future? If this could EVER be true then you should split this out!

I tend to pull the months in to a separate table, That way were not saving "January" over and over again.

2) You really shouldn't store your calculated values. Instead of a tblStats you should have an aggregate query qryStats. The query takes your data, filters on the date range etc...

>> This solves your Append-Query issue :-)

Queries are your friends!
Normally, calculated results are not saved.
> Exception 1: Legal requirement.
> Exception 2: Based on a variable data point such as Tax or Discount rate that might change. (HOWEVER, I would store the rate and use a related table)
> Exception 3: The calculated value does not change except under very controlled conditions. CAUTION HERE, there's usually not much to be gained unless the data set is HUGE and/or the calculation is very complex - hence the speed increase is worth the potential risk of using a wrong value.
> Exception 4:... well... I don't have another exception; however one of the other Experts may have one or two more.

3) Your final report could be based on a crosstab query, the CTQ would be fed by your qryStats. Where [WorkerCode][WorkerName] are row headers, Months would be the Column header, and Stats the data field. The wizard does a decent job on simple CTQ and Allen Browne has a nice article covering CTQ : AB: Crosstab query techniques. Cross tabs are a weak-point in my knowledge and Allen's article has helped point me in the right direction a few times.

Once the query is made and showing the required data, the report can be formatted to your requirements.

[jforbes]

You could use a Crosstab Query for your running totals of the last three months. But I don't think it would be the best fit here. Crosstabs will generate columns based on your data so the amount of columns and their names can vary based on the dataset being selected. Since you have already defined the columns you want to show on your report, and they wont vary, I would create a normal query and include a running totals for each of the last three months for each of your records. You could do this with Inline Queries or create a Aggregate Query to perform the Sum by month and then Join to it.

[rahulapatel]

Thank you guys. Appreciate all the input and suggestions.

@zmbd : Our organization structure is such that the [Worker Code] is going to be associated with the location. Fro example, [worker code] a1 is always going to be California location. The [Worker Name] associated to that worker code can fluctuate on a regular basis (sometimes monthly). I included the location column in that table so when I am doing the reports, I can do individual reports pertaining to each location. And I didn't think a crosstab query would be a feasible solution (note that I'm not familiar with CTQ).

@jforbes : I was thinking in line with that you suggested after I implemented zmbd's original suggestion of normalising and splitting the table. Only thing now is that I'm confused as to using a APPEND query to the tblStats to store all overdues for all months in the same table or to use a MAKE TABLE query each month to store the Overdue numbers for that particular month.

I understand that storing a calculated field is against the norm but in this particular scenario I cant see a solution that avoids storing the count each month in a table because it'll be new data each month and if I do not store the count in a table, I would lose it.

[rahulapatel]

Also, if I use a APPEND query, may be I can use the following SQL statement:

INSERT INTO tblWorkerOverdues ( Unit, Overdues, [Month] )
SELECT tblWorkers.Unit, Count(tblOverdue.Case) AS Ovedues, Format(Now(),"mmm") AS [Month]
FROM tblOverdue RIGHT JOIN tblWorkers ON tblOverdue.Unit = tblWorkers.Unit
WHERE Format(Now(),"mmm") NOT IN (tblWorkerOverdues.month)
GROUP BY tblWorkers.Unit;

The problem with the above query is that it asks the user input for tblWorkerOverdues.Month.

P.S. tblOverdue is the table with all the case data we get every month. tblWorkers is the list of all workers and tblWorkerOverdues is the table that stores all monthly statistics.

[rahulapatel]

Thanks to Tony Hine, Zmbd and jforbes for helping me with your valuable pointers and suggestions. I have accomplished what I wanted. Here is what I did:
APPEND Query to add to the tblWorkerOverdues:

INSERT INTO tblWorkerOverdues ( Unit, Overdues, [Month] )
SELECT tblWorkers.Unit, Count(tblOverdue.Case) AS Ovedues, Month(Now()) AS [Month]
FROM tblOverdue RIGHT JOIN tblWorkers ON tblOverdue.Unit = tblWorkers.Unit
WHERE (((Month(Now())) Not In (SELECT month FROM tblWorkerOverdues)))
GROUP BY tblWorkers.Unit;

Then I created a CTQ. It took a lot of googling to finally achieve the result but I enjoyed the learning process. CTQ code:

TRANSFORM Avg(Query1.Overdues) AS AvgOfOverdues
SELECT Query1.SP, Query1.Wkr, Query1.[Worker Name], Avg(Query1.Overdues) AS [2016 Monthly Average]
FROM Query1
GROUP BY Query1.SP, Query1.Wkr, Query1.[Worker Name]
PIVOT Query1.Month-2 In (1,2,3);
 

I had to use IN statement to have a consistent field name every month for the purpose of the report.

And finally on the report I just had to use Format() and DateADD() to name the fields to last 3 months:

=Format$(DateAdd("m",-2,Now()),"mmm")

=Format$(DateAdd("m",-1,Now()),"mmm")

=Format(Date(),"mmm")

And Voila, I have my report. Thanks again everyone. :)