Calculate user date range without IF statement for every variation

[BZeyger]

Hello,

I have a form which references data from Table_Values.
Table_Values has data for each item and months.

For Example:
Table_Values:
Employee Jan 09 Feb09 Mar09 April09
1 John $45 $50 $60 $70
2 Jane $56 $14 $33 $47
3 Mark $85 $46 $34 $32
4 Mary $13 $87 $25 $64

The form shows this data and calculates the totals in a text field named
txtTotals.

There are also two text boxes that ask the user for a start and end date.
(txtStartDate and txtEndDate)
In addition, there is a run button (cmdRun) that executes the dates.

The form has textboxes liked to the data table:
txtID, txtEmployee, txtJan09, txtFeb09, txtMar09, txtApril09, etc.

Is there a way to process the user selected dated ranges in an easy manner,
instead of creating IF statements for every date variation?

 

[Keven Denen]

Hello,

I have a form which references data from Table_Values.
Table_Values has data for each item and months.

For Example:
Table_Values:
        Employee        Jan 09  Feb09   Mar09   April09
1       John            $45     $50     $60    $70
2       Jane            $56     $14     $33    $47
3       Mark            $85     $46     $34    $32
4       Mary            $13     $87     $25    $64

The form shows this data and calculates the totals in a text field named
txtTotals.

There are also two text boxes that ask the user for a start and end date.
(txtStartDate and txtEndDate)
In addition, there is a run button (cmdRun) that executes the dates.

The form has textboxes liked to the data table:
txtID, txtEmployee, txtJan09, txtFeb09, txtMar09, txtApril09, etc.

Is there a way to process the user selected dated ranges in an easy manner,
instead of creating IF statements for every date variation?

Your table design is very poor. It looks like you are trying to build
a spreadsheet in Access. Access is not Excel, don't try to treat it
like Excel. This is going to make getting at the data very difficult,
as you are noticing.

Your table fields should look more like

Employee Date Value
John 1/09 $45.00
John 2/09 $63.00
Susan 1/09 $48.00
Susan 2/09 $63.00


Then you can run a simple query to pick out the rows within the date
range you are looking for.

Keven

 

[BZeyger]

Unfortunately, I am aware that this database was not setup correctly. This is
something that was passed down to me. There is too much data to start from
scratch. Is there any way to get around this?

 

[Jeff Boyce]

You are facing a "Pay now or pay later" situation.

If you don't take the time now to get it set up in a way that lets you use
Access' features, you (and Access) will be working overtime to come up with
work-arounds.

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or psuedocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Keven Denen]

Unfortunately, I am aware that this database was not setup correctly. This is
something that was passed down to me. There is too much data to start from
scratch. Is there any way to get around this?










- Show quoted text -

What happens in a few months when you need to add more columns for the
new months? Then you'll have to go back in and rewrite your if
statements to account for the new months. You'll have to do this
everytime you update your table designin a year, you'll be rewriting
the database because your if statements are going to be too
complicated/deep to manage.

Really, you would be best to take your data, rearrange it into a
normalized form and start over now.

Keven

Keven

 

[John W. Vinson]

Unfortunately, I am aware that this database was not setup correctly. This is
something that was passed down to me. There is too much data to start from
scratch. Is there any way to get around this?

You can (and should!!!!) migrate this data into a properly structured table.
However, you won't need to retype it.

I'd suggest creating a new table with fields ValuesID, Employee (preferably a
numeric EmployeeID linked to a table of Employees, since you might have three
Johns and two Marys working there), ValueDate (don't use Date as a fieldname),
and TheValue (likewise don't use Value, another reserved word).

Then create a new query using the View... SQL option:

INSERT INTO yournewtable
SELECT Employee, ValueDate, TheValue FROM
(SELECT Employee, #1/1/09#, [Jan 09] FROM Table_Values
WHERE [Jan 09] IS NOT NULL
UNION ALL
SELECT Employee, #2/1/09#, [Feb 09] FROM Table_Values
WHERE [Feb 09] IS NOT NULL
UNION ALL
SELECT Employee, #3/1/09#, [Mar 09] FROM Table_Values
WHERE [MAR 09] IS NOT NULL
UNION ALL
<etc through all your fields>
UNION ALL
SELECT Employee, #12/1/09#, [Dec 09] FROM Table_Values
WHERE [Dec 09] IS NOT NULL);

Run this query and it should "unravel" your wide-flat spreadsheet into a
tall-thin table which can be used much more effectively in Access.