I have an Excel spreadsheet that I input new data into each week. I create a new spreadsheet each week for historical purposes. I now need to consolidated the data to show trends and going forward I will need to keep a running 6 months tally of the numbers. My spreadsheet was never designed for consolidated data, as it contains way to much data and changes occur to the branches and column names. I have been trying to convert these spreadsheets to Access, in hopes that I can create an Access Form that I could use to easily update the tables, then create queries and reports for the historical data needed.

I have attached the spreadsheet I use

My table has "Branch Number", "BU President", "Report Date" and all column headings as on the spreadsheet.
The form I want to create will have "Branch Number" - "Report Date" and then all the Defined columns listed.
The branch numbers are unique and need to be tied to their respective BU Presidents as one of the queries will be defined by the BU President.


My problem:
I can't seem to figure out how to individually link the fields (Columns Names) to the Branch number and Report Date and it produce the same format as my spreadsheet so that no only does it produce the weekly report with data and the consolidated report with data

The results I am looking for are the ablity to create queries based on individual branches, consolidated BU Presidents and their branches, the worst numbers in each of the column catagories. The queries should be based on a weekly data, plus a tally of past weekly numbers up to 6 months.

Please Help Me! Thank You,

Hi Tach

The attcahed example will probably fit the buill?

Come back with any questions

Mike

This looks great. This is very much what I was looking for.

Is there anyway the BU President can be linked to the Branch number/Name,as this does not change. It really doesn't need to be on the form, but needs to be linked so that queries can be ran based on that field.

Thank you so much for your assistance.

Teresa

Hi Teresa.
I did some improvement on Mike's DB. See ATT.


Edited by: jurotek on Wed Aug 22 11:14:30 EDT 2007.

New ATT. SQL problem corrected behind one of the combo boxes

Edited by: jurotek on Wed Aug 22 11:17:06 EDT 2007.

Thank you for your help.
The form itself is exactly what I am looking for. The functionality doesn't quite fit my report needs.
Hopefully I can verbally explain the process behind the excel spreadsheet in hopes that it will help to understand what I need the Access form to perform like.

I receive reports that tell me what payroll errors occurred on various employees with in the branches. I need to be able to record multiple error (problem) for each instance for each branch. and then have a report generated that will breakdown the errors by branches with - in each BU President
The form needs to act like this:

Report Date 8-21-07
Branch 012 - Nashville - Sarnese
Job code incorrect = 2
Paid wrong guard = 15
Branch 014 - Knoxville - Sarnese
Job code incorrect = 4
Holiday Factor = 2
Deduction was/was not needed = 5

The report needs to show like this
BU President
01-01-07 thur 08-30-07
Branch number/name
payroll errors broken into individual categories and displaying the totals for each error type.

I hope I have explained this well enough to where your assistance is not wasted mislead technicalities
Thank you again for all your help
Teresa


Edited by: tacnola on Wed Aug 22 12:22:13 EDT 2007.

Quote:
>>The form itself is exaxtly what I am looking for. The funtionality doesn't quite fit my report needs.<<
Forms are not used as a basis to construct reports. Reports are based on queries with fields from tables added to qbe.
Than you group, count, sum or enter criteria or create calculated controls, anything you like.

I was talking more about the external "paper" reports that I receive that I use to input the data into Access, not necessarily the reports produced by the Access.

I am the worst at verbally explaining what I need. I do appreciate all the help this forum provides.

Thank you,
Teresa

Add, adjust your field names in tables to accommodate your info from external paper reports and make corresponding adjustments on form. The report control on main form will be automatically populated with reports as they are constructed. It has on double click event to open report.

Hi Teresa

Find attached an example of the Report you are after

Cheers

Mike