Turning a Spreadsheet into a Table - and keeping the same abilities.

[voodoo_ca]

I am trying to determine if and how my requirements are possible:

First, I have a database with all kinds of customer and job related information in it.
One of the people here currently uses an excel spreadsheet to track jobs before they are complete along with materials that may be required for the jobs.

Currently he is duplicating work that is already done in my database and deleting the information when he is done with it - I would like to keep/track it.

One of the main concerns is how he uses excel to help him track things.


He uses colour coding in various boxes to know when materials have arrived and when jobs are already booked.
He simply uses excel as a list of the jobs - however he removes the jobs from list once they have been completed.

I was thinking most of the functionality may be possible in a datasheet view of a form.
I thought it might be possible to have cells change colour when values in the change and lines from the list could be removed when dates are added that jobs are complete.

Before I start building, I wanted to see if I was on the right track with this... or if anyone else had any thoughts.

I can give some more detail if required.
Thanks for the help.
Chad

[rpeare]

What you're talking about is called conditional formatting. If a field meets a certain requirement you can change the background color, text color, font, or any other property. HOWEVER, this can not be done in a datasheet view, it can only be done when printing reports, viewing forms, etc. If you are using access strictly as a spreadsheet (you're not using forms, reports, etc) then you will not be able to do the coloring.

[voodoo_ca]

... and thats what I am trying to figure out. Can I accomplish my goal within a database?
The datasheet view is similar to what the person uses now, and it allows the same data entry...
But, for example, when a job is booked the cells cannot be changed in colour the datasheet view is not going to work.
Does my description of what I am trying to do make sense?

Without erasing too much data, I have a snap shot of the spreadsheet.
The first colums I have already in the database, the rest I need to add.
You can see how the first few change in colour as things are booked.
Any thoughts on how I can make things work similarly in Access?

[rpeare]

The only conditional formatting I see on this spreadsheet are the first two columns and possibly the 'SPECIAL' column.

What are your criteria for the yellow, slightly more orange yellow and green in those two columns, and if the special column is supposed to have conditional formatting what governs that?

The other columns that you alternate between a pale pink and white can be done with just regular formatting.

You will have to consider a couple of things like a YES/NO flag for 'completed' or simply have a completed date then only show the items on your 'live' sheet where the completed jobs are not shown.

Also, you are not using a normalized structure. Typically what you'd have is a table for all the possible parts you use on a job, a table for your customers, a table for your basic estimates/order information and a subtable for all the parts you intend to use on the job. Just by looking at your table you can see you have a lot of blank (or null) fields which is a tremendous waste of space. This format may work for you but I'd encourage you to look at normalizing your data in the method I've mentioned. You're also going to have a lot of issues if your list of usable items changes over time you'll have to keep adding columns to your table to allow for it AND change the design of the form every time you change your list of options. I'd strongly suggest you look into normalizing your data (think of it as storing the least possible information.

All that being said here's an example of conditional formatting.

it's very simple but basically if the month of the job date is the same as the current month the second column is highlighted in green, if the month of the job date is in the current month + 1 or current month -11 (to account for december) it's highlighted in a yellow color.

Voodoo_ca.zip

[istari88]

Text boxes and comboBoxes can have up to 50 conditions each for reports in Access 2010, says VBA Programmers Reference 2010, p553.
You may be able to get it done in Report View.

Alternatively, Seems the work for this project could be divided between the 2 of you, one for the data handling and the other for formatting the current data. Data exported from Access to excel is easy. Excel can then use this data to update with conditional formatting.

[voodoo_ca]

I had a reply typed out, but it didnt post for some reason.
rpeare - I like your form example - visually thats what I need the input form to look like because that is what my guy is used to.
I dont have any tables for the information yet (I wasnt sure if this would work the way I wanted) so I can build the correct tables.

The conditioning isnt a big deal - I know that if I have a date field and it is filled, then I can make a job highlight a different colour to show that a job is booked.

Can I limit the information in this form to only jobs that are "current"?
I have 9000+ jobs in the database and I only want to see ones that are not yet installed.
Say I have a "installed" field that is blank when jobs are not done and a date is entered when they are installed.
Can I use your form idea and have installed jobs removed from the list when that date is entered?

Thanks again!
Chad

[rpeare]

yes, you can see my example is based directly on the table, you can modify that statement to include all the fields from the table and if you put an installation date field on the table you can show only the items that are uninstalled by typing IS NULL in the criteria of the query driving the form.

[voodoo_ca]

Thats interesting.
The design of the form that you made - is that a "stock" design, or did you whip that up?

Thanks
Chad

[rpeare]

I put it together in about 5 minutes. I've been doing this kind of thing a long time

[voodoo_ca]

Ya, I figured - I am constantly learning on this stuff and mostly exactly like this.

Can I ask you about the data normalization?

I have a table with my job name information.
Another table with #win, #PD, #DR, #SHUT
The rest is "trim information" - I Have not yet put that in a table.

I was going to make a "trims" table with a row for each of those items.
The table would be joined by way of the JOB ID that is marked out on my sample picture, but it is the first row that you cant see.
If I do things this way, a record will be created whenever a job has any of the trims required.
But as I was just thinking about this - what will happen if a job is in the list and there arent any items for trims yet? with there be boxes?
Also, if I only use 1 type of trim on a job, do the other boxes stay empty and save space?

Thanks
Chad

[rpeare]

Stay away from using special characters in your field names (#, ', (, ), [, etc.) These special characters have a meaning within access and it will just make programming your database more difficult down the line, for instance # indicates a date, ' marks the beginning or end of a text string, etc. Get into the practice of avoiding using them and reserved words like DATE, TIME, etc. Those will also cause you problems. I use compound words and if I want a space I use underscores (_) to indicate them like Num_Win for, I'm assuming, number of windows.

Your TRIM information is going to get lost on a table in a non-normalized structure (like the one you have) because each column already represents a specific type of trim. You have nothing in the screenshot you showed other than possibly CAP COL, VINYL COVE and VINYL CASING. If you intend those to be lists of items then having a table for those items is a great idea.

I'm not sure what you are asking in your last paragraph though, if you are creating a bound form it's based on a table, or a query of a table, when you enter a new item a new record is created in your table, the selection boxes will always be there even if they have nothing in them.

[voodoo_ca]

Understood about the characters - your just seeing what is on the excel sheet I am using for reference - I will change them as required.

Let me try and clarify a bit more cause I am not quite following.

Each of those items starting with MAH COV and ending at 1/2 DRYWALL is a specific trim that could be needed for a job.
When my installation scheduler is looking at the jobs, he will add a number in those coloums depending on what is required for a particular job.
He will simply go over to the correct colum (in excel) and add the number.
At the bottom of the list, he sums the number of pieces that he requires for any jobs that are upcoming and checks that against what we have in stock.

I dont quite understand how to capture these in my database with a better design idea - while still maintaining the abilities that my guy is using in his spreadsheet now.

Thanks
Chad

[rpeare]

You're really limiting yourself with the spreadsheet view but I understand that you've got a process and you want to upset it as little as possible.

So let me see if I understand you. Your installer wants to see how many items in each of those columns he is going to need for a specific day, or a specific week, you then want to compare that against what you have in inventory to see if you have enough to complete, say, 5 jobs that are all scheduled for the same day. So let's say the first five lines on your spreadsheet that you took a screen shot of are all scheduled for the same day, you want to sum the columns to find out how much of each resource you're going to need for that day? If so there are a couple of things you can do, first, on the FORM FOOTER, put a sum of each field. Just add a text box and in the control source put =Sum([FieldName]) where field name is the field you want to total.

Second you can create a report that will show the exact same stuff that's showing on your report but the method would be exactly the same you'd need a report footer or group footer with the same type of formula in it.

Now, if you are also intending to make your database an inventory control database (i.e. tracking receipt and dispensation of these same items) that is an entirely different ball of wax. If you are just comparing the sums (above) to a different inventory system visually, then it's no big deal.

[voodoo_ca]

Ha - you read my mind.
Before I wrote that last reply, I did a sum for the NUM_WIN to see how it would work - and it did.

in its current form here is how the EXCEL sheet works.

1. my installer takes a look at new jobs that have come in and adds their information into the table. This is done by looking at the paperwork and typing it into the excel sheet. Meanwhile, this information (customer name, order number and window/door count) is already added by someone else and tracked in my database.
2. next, he looks at the job specifically (at this point, this new job may get installed 1-2months in the future) and adds into the list any of the trims that will be required when we are going to install the job - so he adds the number of pieces of trim into the list under the corresponding field. he also takes a look at how long it might take to complete the job and adds a value in the last row. He can then see at a glance how many days work he has with the various jobs that are entered.
3. When a job is booked, he changes the colour to green so he knows that job is scheduled already - but because the job may get cancelled or other issues may come about, he keeps these jobs on the list.
4. Sometimes a job gets partially installed - he uses red to make sure he knows that something else still needs to get installed for that customer.
5. When a product is built or received, he changes the colour of that order to yellow so he knows that he can call the customer to install that job.
all of the colour formats are done just using excel to change the field colour.
6. When a job is complete, he deletes the row from the spreadsheet.
7. Totals at the bottom help to show upcoming material requirements for the future.

Maybe my demands/requirements are being done best in the excel sheet?
I just hate the fact that he is duplicating efforts and erasing data that could be used in the future.

If I wasnt stuck with keeping things the way my guy has them, how would you set things up?
Would you have a form to enter the data for each job and then look at your information in a report?

Chad

[rpeare]

I think I would add one field to your data entry screen that's something like PHASE

Then your rows can change color based on the PHASE the order is in rather than basing it on the data that's in the table it seems the easiest solution to what you're doing. Your people are manually changing the color on a spreadsheet, you don't want them to do that and you don't want to use conditional formatting based on a formula, it's basically a users option to highlight which row is which color and you can do that by allowing a combo box with a limited list of phases and each phase corresponds to a color. So for instance in the formulas I have for conditional formatting you'd have instead something like

EXPRESSION IS ([Phase] = "Phase 1")

then set the color you want that corresponds to "phase 1" where the description could be 'waiting for materials', 'partially complete', etc. (though in a truly normalized database you'd likey want a table that stores a code and a code description rather than a text string they are just easier to use like 'PC' for partially complete, 'WM' for Waiting for Materials, it's easier to program conditional statements for 'wm' than it is for a longer text string)