Really Long Expression

  • Thread starter HekaEnt via AccessMonster.com
  • Start date
H

HekaEnt via AccessMonster.com

I am writing a very long expression to gather information on my time card
application and I need to know how I can get more then the alloted amount of
characters in my expression or how I can shorted my expressions length.

----

=IIf([Forms]![HR - Time Card]![Mode (sun)1].Column(1)="Regular",Sum(
[wk1SunType1]),0)+IIf([Forms]![HR - Time Card]![Mode (Mon)1].Column(1)
="Regular",Sum([wk1MonType1]),0)+
IIf([Forms]![HR - Time Card]![Mode (Tue)1].Column(1)="Regular",Sum(
[wk1TueType1]),0)+I
If([Forms]![HR - Time Card]![Mode (Wed)1].Column(1)="Regular",Sum(
[wk1WedType1]),0)+
IIf([Forms]![HR - Time Card]![Mode (Thu)1].Column(1)="Regular",Sum(
[wk1ThurType1]),0)+
IIf([Forms]![HR - Time Card]![Mode (Fri)1].Column(1)="Regular",Sum(
[wk1FriType1]),0)+
IIf([Forms]![HR - Time Card]![Mode (Sat)1].Column(1)="Regular",Sum(
[wk1SatType1]),0)+
IIf([Forms]![HR - Time Card]![Mode (sun)1].Column(1)="Regular / Paid Time
Off",Sum([wk1SunType1]),0)+
IIf([Forms]![HR - Time Card]![Mode (Mon)1].Column(1)="Regular / Paid Time
Off",Sum([wk1MonType1]),0)+
IIf([Forms]![HR - Time Card]![Mode (Tue)1].Column(1)="Regular / Paid Time
Off",Sum([wk1TueType1]),0)+
IIf([Forms]![HR - Time Card]![Mode (Wed)1].Column(1)="Regular / Paid Time
Off",Sum([wk1WedType1]),0)+
IIf([Forms]![HR - Time Card]![Mode (Thu)1].Column(1)="Regular / Paid Time
Off",Sum([wk1ThurType1]),0)+
IIf([Forms]![HR - Time Card]![Mode (Fri)1].Column(1)="Regular / Paid Time
Off",Sum([wk1FriType1]),0)+IIf([Forms]![HR - Time Card]![Mode (Sat)1].Column
(1)="Regular / Paid Time Off",Sum([wk1SatType1]),0+
IIf([Forms]![HR - Time Card]![Mode (sun)1].Column(1)="Regular / Over Time",
Sum([wk1SunType1]),0)+
IIf([Forms]![HR - Time Card]![Mode (Mon)1].Column(1)="Regular / Over Time",
Sum([wk1MonType1]),0)+
IIf([Forms]![HR - Time Card]![Mode (Tue)1].Column(1)="Regular / Over Time",
Sum([wk1TueType1]),0)+
IIf([Forms]![HR - Time Card]![Mode (Wed)1].Column(1)="Regular / Over Time",
Sum([wk1WedType1]),0)+
IIf([Forms]![HR - Time Card]![Mode (Thu)1].Column(1)="Regular / Over Time",
Sum([wk1ThurType1]),0)+
IIf([Forms]![HR - Time Card]![Mode (Fri)1].Column(1)="Regular / Over Time",
Sum([wk1FriType1]),0)+
IIf([Forms]![HR - Time Card]![Mode (Sat)1].Column(1)= “Regular / Over Timeâ€,
Sum([wk1SatType1]),0+
IIf([Forms]![HR - Time Card]![Mode (sun)1].Column(1)="Regular / Non - Paid",
Sum([wk1SunType1]),0)+
IIf([Forms]![HR - Time Card]![Mode (Mon)1].Column(1)=" Regular / Non - Paid ",
Sum([wk1MonType1]),0)+IIf([Forms]![HR - Time Card]![Mode (Tue)1].Column(1)="
Regular / Non - Paid ", Sum([wk1TueType1]),0)+
IIf([Forms]![HR - Time Card]![Mode (Wed)1].Column(1)=" Regular / Non - Paid ",
Sum([wk1WedType1]),0)+
IIf([Forms]![HR - Time Card]![Mode (Thu)1].Column(1)=" Regular / Non - Paid",
Sum([wk1ThurType1]),0)+
IIf([Forms]![HR - Time Card]![Mode (Fri)1].Column(1)=" Regular / Non - Paid",
Sum([wk1FriType1]),0)+
IIf([Forms]![HR - Time Card]![Mode (Sat)1].Column(1)= “Regular / Non - Paidâ€,
Sum([wk1SatType1]),0+
IIf([Forms]![HR - Time Card]![Mode (sun)1].Column(1)="Paid Time Off / Non -
Paid",Sum([wk1SunType1]),0)+
IIf([Forms]![HR - Time Card]![Mode (Mon)1].Column(1)=" Paid Time Off / Non -
Paid ",Sum([wk1MonType1]),0)+IIf([Forms]![HR - Time Card]![Mode (Tue)1].
Column(1)=" Paid Time Off / Non - Paid",Sum([wk1TueType1]),0)+
IIf([Forms]![HR - Time Card]![Mode (Wed)1].Column(1)=" Paid Time Off / Non -
Paid",Sum([wk1WedType1]),0)+
IIf([Forms]![HR - Time Card]![Mode (Thu)1].Column(1)=" Paid Time Off / Non -
Paid",Sum([wk1ThurType1]),0)+
IIf([Forms]![HR - Time Card]![Mode (Fri)1].Column(1)=" Paid Time Off / Non -
Paid",Sum([wk1FriType1]),0)+
IIf([Forms]![HR - Time Card]![Mode (Sat)1].Column(1)=" Paid Time Off / Non -
Paid",Sum([wk1SatType1]),0)
 
M

Marshall Barton

HekaEnt said:
I am writing a very long expression to gather information on my time card
application and I need to know how I can get more then the alloted amount of
characters in my expression or how I can shorted my expressions length.

=IIf([Forms]![HR - Time Card]![Mode (sun)1].Column(1)="Regular",Sum(
[wk1SunType1]),0)+IIf([Forms]![HR - Time Card]![Mode (Mon)1].Column(1)
="Regular",Sum([wk1MonType1]),0)+
IIf([Forms]![HR - Time Card]![Mode (Tue)1].Column(1)="Regular",Sum(
[wk1TueType1]),0)+I
If([Forms]![HR - Time Card]![Mode (Wed)1].Column(1)="Regular",Sum(
[wk1WedType1]),0)+
IIf([Forms]![HR - Time Card]![Mode (Thu)1].Column(1)="Regular",Sum(
[wk1ThurType1]),0)+
IIf([Forms]![HR - Time Card]![Mode (Fri)1].Column(1)="Regular",Sum(
[wk1FriType1]),0)+
IIf([Forms]![HR - Time Card]![Mode (Sat)1].Column(1)="Regular",Sum(
[wk1SatType1]),0)+
IIf([Forms]![HR - Time Card]![Mode (sun)1].Column(1)="Regular / Paid Time
Off",Sum([wk1SunType1]),0)+
IIf([Forms]![HR - Time Card]![Mode (Mon)1].Column(1)="Regular / Paid Time
Off",Sum([wk1MonType1]),0)+
IIf([Forms]![HR - Time Card]![Mode (Tue)1].Column(1)="Regular / Paid Time
Off",Sum([wk1TueType1]),0)+
IIf([Forms]![HR - Time Card]![Mode (Wed)1].Column(1)="Regular / Paid Time
Off",Sum([wk1WedType1]),0)+
IIf([Forms]![HR - Time Card]![Mode (Thu)1].Column(1)="Regular / Paid Time
Off",Sum([wk1ThurType1]),0)+
IIf([Forms]![HR - Time Card]![Mode (Fri)1].Column(1)="Regular / Paid Time
Off",Sum([wk1FriType1]),0)+IIf([Forms]![HR - Time Card]![Mode (Sat)1].Column
(1)="Regular / Paid Time Off",Sum([wk1SatType1]),0+
IIf([Forms]![HR - Time Card]![Mode (sun)1].Column(1)="Regular / Over Time",
Sum([wk1SunType1]),0)+
IIf([Forms]![HR - Time Card]![Mode (Mon)1].Column(1)="Regular / Over Time",
Sum([wk1MonType1]),0)+
IIf([Forms]![HR - Time Card]![Mode (Tue)1].Column(1)="Regular / Over Time",
Sum([wk1TueType1]),0)+
IIf([Forms]![HR - Time Card]![Mode (Wed)1].Column(1)="Regular / Over Time",
Sum([wk1WedType1]),0)+
IIf([Forms]![HR - Time Card]![Mode (Thu)1].Column(1)="Regular / Over Time",
Sum([wk1ThurType1]),0)+
IIf([Forms]![HR - Time Card]![Mode (Fri)1].Column(1)="Regular / Over Time",
Sum([wk1FriType1]),0)+
IIf([Forms]![HR - Time Card]![Mode (Sat)1].Column(1)= “Regular / Over Time”,
Sum([wk1SatType1]),0+
IIf([Forms]![HR - Time Card]![Mode (sun)1].Column(1)="Regular / Non - Paid",
Sum([wk1SunType1]),0)+
IIf([Forms]![HR - Time Card]![Mode (Mon)1].Column(1)=" Regular / Non - Paid ",
Sum([wk1MonType1]),0)+IIf([Forms]![HR - Time Card]![Mode (Tue)1].Column(1)="
Regular / Non - Paid ", Sum([wk1TueType1]),0)+
IIf([Forms]![HR - Time Card]![Mode (Wed)1].Column(1)=" Regular / Non - Paid ",
Sum([wk1WedType1]),0)+
IIf([Forms]![HR - Time Card]![Mode (Thu)1].Column(1)=" Regular / Non - Paid",
Sum([wk1ThurType1]),0)+
IIf([Forms]![HR - Time Card]![Mode (Fri)1].Column(1)=" Regular / Non - Paid",
Sum([wk1FriType1]),0)+
IIf([Forms]![HR - Time Card]![Mode (Sat)1].Column(1)= “Regular / Non - Paid”,
Sum([wk1SatType1]),0+
IIf([Forms]![HR - Time Card]![Mode (sun)1].Column(1)="Paid Time Off / Non -
Paid",Sum([wk1SunType1]),0)+
IIf([Forms]![HR - Time Card]![Mode (Mon)1].Column(1)=" Paid Time Off / Non -
Paid ",Sum([wk1MonType1]),0)+IIf([Forms]![HR - Time Card]![Mode (Tue)1].
Column(1)=" Paid Time Off / Non - Paid",Sum([wk1TueType1]),0)+
IIf([Forms]![HR - Time Card]![Mode (Wed)1].Column(1)=" Paid Time Off / Non -
Paid",Sum([wk1WedType1]),0)+
IIf([Forms]![HR - Time Card]![Mode (Thu)1].Column(1)=" Paid Time Off / Non -
Paid",Sum([wk1ThurType1]),0)+
IIf([Forms]![HR - Time Card]![Mode (Fri)1].Column(1)=" Paid Time Off / Non -
Paid",Sum([wk1FriType1]),0)+
IIf([Forms]![HR - Time Card]![Mode (Sat)1].Column(1)=" Paid Time Off / Non -
Paid",Sum([wk1SatType1]),0)
Click to expand...


Attempting to shock some sense into this monstrosity.

This looks like the contortions you would go through if you
had a spreadsheet. Databases are supposed to add some
sanity to to the situation, not make it worse.

The first thing you need to do is stop thinking spreadsheet
and start thinking relational database by normalizing your
tables so that ugly mess becomes unnecessary. A table
should never have fields like Mon, Tue, etc. Instead you
should have a separate table with a record for each
date/person. Then you could just Sum the values over a date
range. A database should have many tables with a few
columns and lots of records, NOT a few tables with lots of
columns and few records.

To answer your question directly, you can not exceed the
limit on the length of an expression. About all you can do
is change all the names to something shorter, with no spaces
or other funky characters. If the query is based on a
single table, you can drop the table name before each field
name or you could alias the table name to a single
character. Even if you do all these things and cram
everything into the expression, it will still be an
unmanagable mess.
 
J

John Nurick

The names of the fields and controls indicate that your tables have
groups of fields for Sunday, Monday ... Saturday. As you have
discovered, this gets very messy. The solution is to normalise your
data, after which task like this can be done with comparatively simple
SQL expressions.

In the interim, you can try band-aids such as shortening the names of
the objects, and perhaps replacing the expression with a custom VBA
function.

I am writing a very long expression to gather information on my time card
application and I need to know how I can get more then the alloted amount of
characters in my expression or how I can shorted my expressions length.

----

=IIf([Forms]![HR - Time Card]![Mode (sun)1].Column(1)="Regular",Sum(
[wk1SunType1]),0)+IIf([Forms]![HR - Time Card]![Mode (Mon)1].Column(1)
="Regular",Sum([wk1MonType1]),0)+
IIf([Forms]![HR - Time Card]![Mode (Tue)1].Column(1)="Regular",Sum(
[wk1TueType1]),0)+I
If([Forms]![HR - Time Card]![Mode (Wed)1].Column(1)="Regular",Sum(
[wk1WedType1]),0)+
IIf([Forms]![HR - Time Card]![Mode (Thu)1].Column(1)="Regular",Sum(
[wk1ThurType1]),0)+
IIf([Forms]![HR - Time Card]![Mode (Fri)1].Column(1)="Regular",Sum(
[wk1FriType1]),0)+
IIf([Forms]![HR - Time Card]![Mode (Sat)1].Column(1)="Regular",Sum(
[wk1SatType1]),0)+
IIf([Forms]![HR - Time Card]![Mode (sun)1].Column(1)="Regular / Paid Time
Off",Sum([wk1SunType1]),0)+
IIf([Forms]![HR - Time Card]![Mode (Mon)1].Column(1)="Regular / Paid Time
Off",Sum([wk1MonType1]),0)+
IIf([Forms]![HR - Time Card]![Mode (Tue)1].Column(1)="Regular / Paid Time
Off",Sum([wk1TueType1]),0)+
IIf([Forms]![HR - Time Card]![Mode (Wed)1].Column(1)="Regular / Paid Time
Off",Sum([wk1WedType1]),0)+
IIf([Forms]![HR - Time Card]![Mode (Thu)1].Column(1)="Regular / Paid Time
Off",Sum([wk1ThurType1]),0)+
IIf([Forms]![HR - Time Card]![Mode (Fri)1].Column(1)="Regular / Paid Time
Off",Sum([wk1FriType1]),0)+IIf([Forms]![HR - Time Card]![Mode (Sat)1].Column
(1)="Regular / Paid Time Off",Sum([wk1SatType1]),0+
IIf([Forms]![HR - Time Card]![Mode (sun)1].Column(1)="Regular / Over Time",
Sum([wk1SunType1]),0)+
IIf([Forms]![HR - Time Card]![Mode (Mon)1].Column(1)="Regular / Over Time",
Sum([wk1MonType1]),0)+
IIf([Forms]![HR - Time Card]![Mode (Tue)1].Column(1)="Regular / Over Time",
Sum([wk1TueType1]),0)+
IIf([Forms]![HR - Time Card]![Mode (Wed)1].Column(1)="Regular / Over Time",
Sum([wk1WedType1]),0)+
IIf([Forms]![HR - Time Card]![Mode (Thu)1].Column(1)="Regular / Over Time",
Sum([wk1ThurType1]),0)+
IIf([Forms]![HR - Time Card]![Mode (Fri)1].Column(1)="Regular / Over Time",
Sum([wk1FriType1]),0)+
IIf([Forms]![HR - Time Card]![Mode (Sat)1].Column(1)= “Regular / Over Time”,
Sum([wk1SatType1]),0+
IIf([Forms]![HR - Time Card]![Mode (sun)1].Column(1)="Regular / Non - Paid",
Sum([wk1SunType1]),0)+
IIf([Forms]![HR - Time Card]![Mode (Mon)1].Column(1)=" Regular / Non - Paid ",
Sum([wk1MonType1]),0)+IIf([Forms]![HR - Time Card]![Mode (Tue)1].Column(1)="
Regular / Non - Paid ", Sum([wk1TueType1]),0)+
IIf([Forms]![HR - Time Card]![Mode (Wed)1].Column(1)=" Regular / Non - Paid ",
Sum([wk1WedType1]),0)+
IIf([Forms]![HR - Time Card]![Mode (Thu)1].Column(1)=" Regular / Non - Paid",
Sum([wk1ThurType1]),0)+
IIf([Forms]![HR - Time Card]![Mode (Fri)1].Column(1)=" Regular / Non - Paid",
Sum([wk1FriType1]),0)+
IIf([Forms]![HR - Time Card]![Mode (Sat)1].Column(1)= “Regular / Non - Paid”,
Sum([wk1SatType1]),0+
IIf([Forms]![HR - Time Card]![Mode (sun)1].Column(1)="Paid Time Off / Non -
Paid",Sum([wk1SunType1]),0)+
IIf([Forms]![HR - Time Card]![Mode (Mon)1].Column(1)=" Paid Time Off / Non -
Paid ",Sum([wk1MonType1]),0)+IIf([Forms]![HR - Time Card]![Mode (Tue)1].
Column(1)=" Paid Time Off / Non - Paid",Sum([wk1TueType1]),0)+
IIf([Forms]![HR - Time Card]![Mode (Wed)1].Column(1)=" Paid Time Off / Non -
Paid",Sum([wk1WedType1]),0)+
IIf([Forms]![HR - Time Card]![Mode (Thu)1].Column(1)=" Paid Time Off / Non -
Paid",Sum([wk1ThurType1]),0)+
IIf([Forms]![HR - Time Card]![Mode (Fri)1].Column(1)=" Paid Time Off / Non -
Paid",Sum([wk1FriType1]),0)+
IIf([Forms]![HR - Time Card]![Mode (Sat)1].Column(1)=" Paid Time Off / Non -
Paid",Sum([wk1SatType1]),0)

-----------
Click to expand...
 
H

HekaEnt via AccessMonster.com

I shortened the names of things and have reduced my script down 1400
characters, now i am around 400 over still. how do i create these band-aids
you speak of, for like [Forms]! and 'Column'. if i cant shorten these then
how would i write a custome VBA script to complete this funtion?

John said:
The names of the fields and controls indicate that your tables have
groups of fields for Sunday, Monday ... Saturday. As you have
discovered, this gets very messy. The solution is to normalise your
data, after which task like this can be done with comparatively simple
SQL expressions.

In the interim, you can try band-aids such as shortening the names of
the objects, and perhaps replacing the expression with a custom VBA
function.
I am writing a very long expression to gather information on my time card
application and I need to know how I can get more then the alloted amount of
Click to expand...
[quoted text clipped - 70 lines]
-----------
Click to expand...
Click to expand...
 
H

HekaEnt via AccessMonster.com

now i have completely streamlined my code down to basically the smallest i
can have it and it is 1806 characters, it has no spaces and access is still
saying it is too long, yet access' expression limit is 2048, has anyone had
this problem and if so how did you over come it.
attached is my script

----------

=IIf([Forms]![TC]![sm1].Column(1)="R",Sum([s2]),0)
+IIf([Forms]![TC]![mm1].Column(1)="R",Sum([m2]),0)
+IIf([Forms]![TC]![tm1].Column(1)="R",Sum([t2]),0)
+IIf([Forms]![TC]![wm1].Column(1)="R",Sum([w2]),0)
+IIf([Forms]![TC]![hm1].Column(1)="R",Sum([h2]),0)
+IIf([Forms]![TC]![fm1].Column(1)="R",Sum([f2]),0)
+IIf([Forms]![TC]![am1].Column(1)="R",Sum([a2]),0)
+IIf([Forms]![TC]![sm1].Column(1)="R/P",Sum([s2]),0)
+IIf([Forms]![TC]![mm1].Column(1)="R/P",Sum([m2]),0)
+IIf([Forms]![TC]![tm1].Column(1)="R/P",Sum([t2]),0)
+IIf([Forms]![TC]![wm1].Column(1)="R/P",Sum([w2]),0)
+IIf([Forms]![TC]![hm1].Column(1)="R/P",Sum([h2]),0)
+IIf([Forms]![TC]![fm1].Column(1)="R/P",Sum([f2]),0)
+IIf([Forms]![TC]![am1].Column(1)="R/P",Sum([a2]),0)
+IIf([Forms]![TC]![sm1].Column(1)="R/O",Sum([s2]),0)
+IIf([Forms]![TC]![mm1].Column(1)="R/O",Sum([m2]),0)
+IIf([Forms]![TC]![tm1].Column(1)="R/O",Sum([t2]),0)
+IIf([Forms]![TC]![wm1].Column(1)="R/O",Sum([w2]),0)
+IIf([Forms]![TC]![hm1].Column(1)="R/O",Sum([h2]),0)
+IIf([Forms]![TC]![fm1].Column(1)="R/O",Sum([f2]),0)
+IIf([Forms]![TC]![am1].Column(1)=“R/Oâ€,Sum([a2]),0)
+IIf([Forms]![TC]![sm1].Column(1)="R/N",Sum([s2]),0)
+IIf([Forms]![TC]![mm1].Column(1)="R/N",Sum([m2]),0)
+IIf([Forms]![TC]![tm1].Column(1)="R/N",Sum([t2]),0)
+IIf([Forms]![TC]![wm1].Column(1)="R/N",Sum([w2]),0)
+IIf([Forms]![TC]![hm1].Column(1)="R/N",Sum([h2]),0)
+IIf([Forms]![TC]![fm1].Column(1)="R/N",Sum([f2]),0)
+IIf([Forms]![TC]![am1].Column(1)=“R/Nâ€,Sum([a2]),0)
+IIf([Forms]![TC]![sm1].Column(1)="P/N",Sum([s2]),0)
+IIf([Forms]![TC]![mm1].Column(1)="P/N",Sum([m2]),0)
+IIf([Forms]![TC]![tm1].Column(1)="P/N",Sum([t2]),0)
+IIf([Forms]![TC]![wm1].Column(1)="P/N",Sum([w2]),0)
+IIf([Forms]![TC]![hm1].Column(1)="P/N",Sum([h2]),0)
+IIf([Forms]![TC]![fm1].Column(1)="P/N",Sum([f2]),0)
+IIf([Forms]![TC]![am1].Column(1)="P/N",Sum([a2]),0)

----------
I shortened the names of things and have reduced my script down 1400
characters, now i am around 400 over still. how do i create these band-aids
you speak of, for like [Forms]! and 'Column'. if i cant shorten these then
how would i write a custome VBA script to complete this funtion?
The names of the fields and controls indicate that your tables have
groups of fields for Sunday, Monday ... Saturday. As you have
Click to expand...
[quoted text clipped - 16 lines]
Please respond in the newgroup and not by email.
Click to expand...
Click to expand...
 
J

John Nurick

If I understand the situation, the simple VBA approach is along these
lines. I've assumed the code is in a form's or report's module: I think
things may get much nastier if you want to call the function in a SQL
statement.

Public Function CalculateWhateverItIs() As WhateverTypeYouNeed
Dim Buf As WhateverTypeYouNeed

If [Forms]![TC]![sm1].Column(1)="R" Then
Buf = DSum("s2", "MyTableOrQuery")
End If

If [Forms]![TC]![mm1].Column(1)="R" Then
Buf = Buf + DSum("m2", "MyTableOrQuery"])
End If

If [Forms]![TC]![tm1].Column(1)="R" Then
Buf = Buf + DSum("t2", "MyTableOrQuery")
End If

and so on to exhaustion (and debugging nightmares). But I strongly
advise against going down this road. Instead, bite the bullet and
normalise your data.



I shortened the names of things and have reduced my script down 1400
characters, now i am around 400 over still. how do i create these band-aids
you speak of, for like [Forms]! and 'Column'. if i cant shorten these then
how would i write a custome VBA script to complete this funtion?

John said:
The names of the fields and controls indicate that your tables have
groups of fields for Sunday, Monday ... Saturday. As you have
discovered, this gets very messy. The solution is to normalise your
data, after which task like this can be done with comparatively simple
SQL expressions.

In the interim, you can try band-aids such as shortening the names of
the objects, and perhaps replacing the expression with a custom VBA
function.
I am writing a very long expression to gather information on my time card
application and I need to know how I can get more then the alloted amount of
Click to expand...
[quoted text clipped - 70 lines]
-----------
Click to expand...
Click to expand...
Click to expand...
 
J

John Vinson

now i have completely streamlined my code down to basically the smallest i
can have it and it is 1806 characters, it has no spaces and access is still
saying it is too long, yet access' expression limit is 2048, has anyone had
this problem and if so how did you over come it.
Click to expand...

I've never had this problem... because I've never had to work with
such a very badly denormalized database.

You've been advised to restructure your tables so that they are
properly normalized. Doing so will solve (by simply avoiding!) this
problem, and make many, many other chores much easier.

If you choose not to take this good advice, then I'd suggest creating
a new, blank database; import everything EXCEPT this form; create this
form from scratch; and copy and paste this expression into the control
source.

Or, write a VBA function which opens a Recordset and loops through the
denormalized fields adding the values up.

John W. Vinson[MVP]
 
H

HekaEnt via AccessMonster.com

ok then, how would I normalize my database?

John said:
I've never had this problem... because I've never had to work with
such a very badly denormalized database.

You've been advised to restructure your tables so that they are
properly normalized. Doing so will solve (by simply avoiding!) this
problem, and make many, many other chores much easier.

If you choose not to take this good advice, then I'd suggest creating
a new, blank database; import everything EXCEPT this form; create this
form from scratch; and copy and paste this expression into the control
source.

Or, write a VBA function which opens a Recordset and loops through the
denormalized fields adding the values up.

John W. Vinson[MVP]
Click to expand...
 
H

HekaEnt via AccessMonster.com

Since i guess i am taking the wrong step i want to correct it before it gets
worse and get the best outcome.

The information below is the information that i need to send to my HR
department.

first name
last name
date
hours
type of hours
shift


this data then needs to calculate per week,for week 1 and week 2 defined by
the type of hours they selected

how many hours are regular hours
pto
holiday
non-paid
overtime

then summed up how many hours were worked for pay period (week 1 + week2)

how many hours are regular hours
pto
holiday
non-paid
overtime


attached is a link to an example one that we are using as a papered system
(which we are moving away from).

www.hekaent.com/Customers/P/PolyCast/images/example.jpg

the information at the bottom does not need to be on the form itself but
needs to be accessible through a query or report for the user to view their
time.

on the form the user needs to be able to enter their data for the hours they
worked in type 1 and type 2, then choose the type of hours they worked from a
drop down.

my question is how would i set up my database to best fit this need.
 
J

John Vinson

Since i guess i am taking the wrong step i want to correct it before it gets
worse and get the best outcome.
Click to expand...

I'm going to be tied up for a couple of days and won't be able to
respond until the weekend... but I am trying to figure out what to
advise you here.

This "really long expression" has taken you about 3.2 miles down a
rough, rocky wrong road though... <g>

John W. Vinson[MVP]
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Getting rid of a Having clause 1
Overflow 2
DATABASE REPORT/CROSSTAB QUERY HELP 2
Summing 2 columns based on variables 3
Sorting day names chronologically 5
Sumif from two criteria in one column 4
Time Card 1
Next weekday ... 3

Top