How to extract data from a table

[mlai08]

I have a table which captures the monthly headcount by resource type & by department, layout as follows:

DeptID Internal External GR Month MonthNum Year
TSS 7 0 0 Sep 11 2011
CS 115 33 20 Sep 11 2011
DTI 93 11 0 Sep 11 2011
TSS 8 1 0 Oct 12 2011
CS 120 31 21 Oct 12 2011
DTI 92 10 0 Oct 12 2011

I have difficulty showing the data in a matrix format with DeptID and resource type as row headers and Month as column headers like this:

Sept 2011 Oct 2011
TSS Internal 7 8
External 0 0
GR 0 0

CS Internal 115 120
External 33 31
GR 20 21

DTI Internal 93 92
External 11 10
GR 0 0

I have tried cross tab query and pivot table by exporting the data to Excel but none of these attempts work. As data will be appended to the table every month and I don't really want to change the table structure to include fields for the month (Sept, Oct, Nov ....).

(The enclosed picture shows a better layout of the table & report)

Can any expert on the forum give me a hand?

Thanks a bunch.

 

[PNGBill]

I have some complicated data to report by Month and Year.

First I use a Union (UnionAll) Query - in our case data is on many tables.
The Union query allows all the data to be lined up, so to speak.

Then a Crosstab Query

Then a Report using Grouping and Sorting with Calculated Controls

Each year is a new page in the report.

If you wish, a report can be exported to xl but we are happy with the report in Preview which is then viewable on your screen.

The report handles Month columns.

 

[mlai08]

Can you elaborate on how a union query can help in my case?

 

[PNGBill]

Not sure a Union Query is needed for your data as it is all on one table.

A Union Query can collect data from different queries and assemble it into like groups eg you have Drs on one table and Crs on another. A Union Query can put them into one field which can then be output and or Sumed (assuming you have Positive and Negative values accordingly.)

My point was to show how different tools can be used to achieve the end result.

Assuming your database structure is "Normal" then maybe a Report using Grouping and Sorting will give you what you want.
Another reason I use a Report is Forms did not have Grouping and Sorting benefits like Reports do.

 

[mlai08]

Thanks for your comments but I I did try to use Grouping/Sorting to run Access report but can not achieve what I want. If you open the table & the report in my enclosed image, you will see what I mean. The issue is I can not make the Month as column header and the other fields as row header.

 

[PNGBill]

Are you able to post a sample database with just the relevant parts and after it has been compacted and repaired to get less then 1mb ?

 

[mlai08]

Are you able to post a sample database with just the relevant parts and after it has been compacted and repaired to get less then 1mb ?

I have enclosed a sample db with a table with resource count by month & year. Any help you may provide would be much appreciated.

The required report is included in my previous attachment.

Thanks

 

[PNGBill]

I will look at this later this evening.

What version access do you use ?

 

[mlai08]

I use 2003.

 

[PNGBill]

Some issues with your tblRscCountByMonth

No Primary Key - this must be a Unique record field. Common methd is to use AutoNumber but can be a String (text) field.
You will not be able to do very much with your data unless you assign, add a Primary Key Field.

External, Month and year are all Reserved Words and shouldn't be used.
DateRcd, MonthNum is a good method. I suggest you do the same to Internal, External, Month and Year.

How do you get this data ?

Is it raw data or have you "collected" it from other tables in your database ?

 

[PNGBill]

Please confirm if the data you supplied matches the sample report on your first post ??
eg CS. The same data appears to be repeated for Sep, Oct, Nov and Dec ??.
I only see 115, 33 & 20. No sign of 120, 31 or 21 ??

 

[mlai08]

Some issues with your tblRscCountByMonth

No Primary Key - this must be a Unique record field. Common methd is to use AutoNumber but can be a String (text) field.
You will not be able to do very much with your data unless you assign, add a Primary Key Field.

External, Month and year are all Reserved Words and shouldn't be used.
DateRcd, MonthNum is a good method. I suggest you do the same to Internal, External, Month and Year.

How do you get this data ?

Is it raw data or have you "collected" it from other tables in your database ?

Thank you for your comments. I did not follow some of the Access good practices because this is just a sample table. I have now rename the fields and add an Autonumber primary key to the table. For simplicity purposes, I only include 3 DeptIDs and 2 month's data. The sample report with matching information is also enclosed.

To give you more info, I have set up a form for users to run a module to record the resource information as of the end of each month and append those records to the RscCountByMonth table. The month to month figures will be the same if there is no change on the employee # by end of the month.

My focus here is to find out how I can turn this data into the format as indicated on the ReportSample file. The report will provide comparisons on resource changes by department over the year at a glance. It could be in Access or Excel format.

 

[keikoebe]

Please confirm if the data you supplied matches the sample report on your first post ??
eg CS. The same data appears to be repeated for Sep, Oct, Nov and Dec ??.
I only see 115, 33 & 20. No sign of 120, 31 or 21 ??

This was my first thought

 

[PNGBill]

Have a look at DbSample1x.mdb - attached.

I suggest you create a Macro to run the following queries in this order:

qrytblDataSortDelete
qryInternalDataAppend
qryExternalDataAppend
qryGRDataAppend
qrytblDataSort

By running qry's in the above order you Delete all records from tblDataSort then Append records one group at a time to the table.
This gives you the Latest Data.
Final qrytblDataSort will sort the new table's data, nearly how you wish.

I am a bit short of time but it should be easier to get the report you want from the final query, qrytblDataSort.

Let me know if still not working out and there may be other advice that is better and or more efficient.

The first Append Query could have been a Make Table Query with the following two Append queries adding data to the new table and avoid the need to run a Delete Query first.
Having a permament table does make it easier to format the fileds etc as the table is always there, just doesn't have data.

With the Macro, turn off warnings and whole process will be "seamless'

 

[mlai08]

Thank you for the suggestion but how can we display the month & year as column headers as shown on my report format. This is how the end users want to see. I don't think this can be achieved with Access reports.

I have now created a module to export data to Excel based on criteria selected by users on a form. The module will automate a pivot table set up which output a format close to what we want. Sample spreadsheet enclosed for your reference. (Forget about the numbers since they are for demo purposes).

Anyway, thanks a lot for your time.