I have yet a new issue that is the only factor stopping me from adding on the last feature to the database; all help is very much appreciated!
In my database is are two tables: one for inserting contact information and employee status, and the other documenting work history. I created a query that contains only the most recent data entered and from that query I created a cross-tab query that reads the data in the query and returns the list of all the names as the row headers, the status as the column header, and the totalCount as the value. Whenever the cross-tab is run, it works as it's suppose to by supplying all the names in the Y-axis, all the status types in the X-axis, and the count of each type in the columns. When a user modifies the status for the employee the query is updated, sometimes the status may change or a new status type is added to. For example the query may return:
Ex. 1
----------------------------------------------------------------------------------------
Contacted Accepted Declined
John 1
Mary 1
Adam 1
----------------------------------------------------------------------------------------
Then if a new name is added to the table the cross-tab query is updated:
Ex. 2
----------------------------------------------------------------------------------------------------
Contacted Accepted Declined Offer Extended
John 1
Mary 1
Adam 1
Sarah 1
---------------------------------------------------------------------------------------------------
Now that all works. The problem is that when it comes time to making a report I'm lost on about how to make the report dynamic in the sense of having it display the data in Ex. 1 but when new information is added having it updated to display the data in Ex. 2 automatically. Eventually I would like to add subtotals in the report to show the current number of status_types there are for example:
Contacted Accepted Declined Offer Extended
John 1
Mary 1
Adam 1
Sarah 1
Joseph 1
Sally 1
Eric 1
---------------------------------------------------------------------------------------------------
Subtotal: 2 1 2 2
I assume I will have to do some VBA and perhaps SQL magic but I don't know about how to do that. Can anyone provide me wuth some code snippets (if any) or direct me in a new direction from how I am approaching this?