How can I do this following rules of a relational database?

[dornm]

I am trying to convert a work management system in Excel to Access so I can scale it up to handle a larger group. Six Excel sheets isn't too hard to maintain but 200 is if you get my point.

The Excel sheet is laid as below and is used to estimate how many man hours will be spent on a line item for a given month:

Item       Nov   Dec   Jan   Feb   Mar...Oct
ABC        10    10     5
BCD               5    15    15     15 
CDE              10    10    20      5

The sheet has a macro that will roll the data to the left at the end of the month so the spreadsheet keeps track of data on a 12 month rolling basis.

I've come up with the table structure for most of the data that the database needs, but I can't think of an easy way to mimic Excel without defining fields such as 04Nov, 04Dec, 05Jan, etc...

I was thinking of a table called tblWorkItem with the following fields:
IDWorkItem
Name_ID
WorkTypeID
WorkName

Then a child table perhaps called tblManHours with
IDManHours
WorkItem_ID
Month
Year
Hours

I'm not sure how I could build an interface using that structure that is as easy to use as the excel interface. Has anyone tackled something similar that could offer some advice?

Thanks in advance,

Matt

 

[BrianB]

Re: How can I do this following rules of a relational databa

I do finance analysis and reporting for a large co. and have to do this sort thing all the time. Although my requirements are sure to be different to yours. Hopefully a few comments might help.

1, You basic structure looks Ok. I don't see a field in the 'hours' table to differentiate between your Groups.

2. You do not say where your raw data is coming from (user input or other kind of file). I usually get mine as a .csv download from a corporate database, so I occasionally get problems with data corruption to handle. The file may look OK but does not add up. I am still using Office 97 here, and get problems with column data formatting Excel to Access etc.

3. I use lots of VBA. Typically saving 90% - often more - of time taken for the manual process. eg. recently, 1 day job reduced to 10 minutes.

4. In this scenario my usual process is :-
Text File -> Excel worksheet -> Access -> Unformatted Excel worksheet -> Pivot Table -> Final report.
I keep 2 or 3 sets of separate code for this. Excel textfile -> Access processing -> Excel (if required)

This enables me (with VBA code) to check the overall totals at each stage. If I get wrong totals in the final report I do not need to re-run the whole process again (and again ..........). The numerous changes of company and therefore data structure during the year are easily handled. Don't need to change everything usually just the Access table & add/delete rows in the final report !! Usually just a few seconds to tweak VBA code.

I use Pivot Tables only as an interim. We cannot format them anyway. It is easy to use various lookup formulas in the final report and enable a doubleclick on a number there to get a drilldown of the numbers making that total. Our managers usually only want to see reasons for large variancess month on month.

5. To transfer data to Excel from Access I use a "Make Table" query in Access and code to make it into a .xls file. Firstly the code is simplified everywhere. Secondly, Access is faster at this stage. Third it makes for easy checking of data in case of problems. Fourth, making a worksheet to include with the reports avoids any linking problems. All of this, of course, is variable depending on circumstances.

6. Should be number 1 really. When defining your method always keep in mind the requirements and skills of your "Customers". I do not need to do a lot of coding for mine because I put it 'all' in Excel which they use all the time. Do not try to do everything at once. Keep things simple to start with and get customer feedback on what further developments are required. You save hours of time making changes, and appear to "deliver" quicker.

Hope this helps.

 

[mdmilner]

Re: How can I do this following rules of a relational databa

The way you do this is by putting your Months + Labor values into a separate three field table as records. Ignoring other data also in your tables.

Fields would be (rename these are descriptive):

First Table
Line Item
UniqueKey

Second Table
NonUniqueKey
Month
LaborValue

You'd build a 1 to Many relationship between a unique key in your original line item table and the NonUniqueKey in the second table.

Step 2 - make a query based on the above two tables. Add all fields (QBE wizard does this easily)

Step 3 - Make a Crosstab Query based on the above query. Use the Line item as a Row Heading, the Month Value as a Column Heading, and the Sum of the Labor Value as your value.

What this should do is give you something that appears "like a table" with one row per item, and a number of columns based on how many months the line item had entries for. If you only had 3 months of labor, you'd ONLY see those three months.

Make the Month Field values as specific as possible (You can drop actual dates such as 1/1/2004 and then use something like the Month and/or Year functions to reformat the appearance of the row names. You can also custom format the field data types to display just what you want.

From here, what you do is (still in the QBE wizard) Add a parameter. Parameters are added by dragging a field into the design mode, then selecting 'Where' and adding a parameter. >=#1/1/2003# would get everything starting in 2003 when looking at the dates. You can make this more complex by having it dynamically identify Today and figuring out when 12 months ago 'was'. I'd recommend trying the above first and then working on getting more complex.

I forgot to add. Remember the query above that has all the fields (you based the crosstab query on it). That's useful as the recordsource for a form you build to edit the data. (You can build forms and use tables/queries as recordsources. Gives you built in tools to move through the records and create new ones instead of directly editing the tables)

Mike

 

[dornm]

Re: How can I do this following rules of a relational databa

Mike,

I followed your suggestions and created a crosstab query that mimics the look of Excel. However, crosstab queries are not updatable so I couldn't edit data in the query. Is there another way around this?

Thanks,

Matt

 

[mdmilner]

Re: How can I do this following rules of a relational databa

You should use your two original tables as the recordsource for a form (not the Crosstab). Rebuild your query as a non-Crosstab and either use that query as the recordsource OR (1) save the query. 2) look at it in SQL view 3) Paste the SQL view into the recordsource property of a form in design mode)

The former is easier to do - latter is slicker.

Once you set this up, make sure your form has bound text boxes for all your fields (if you use the query as a recordsource AND let the wizard build your form, it will do it this way automatically.

From there I'd fiddle with it until it looked the way I wanted it but basically, that should give you an input form.
-
A slightly more complex but better way to do this is to use a form/subform combination. Form based on first table, Subform based on second. Make sure the table relationships are setup first, and when you add the subform, it should setup the parent/child links between the table. You'll have a form with record navigation buttons and then an "inset" subform also with it's own record navigation buttons.

Mike