problems working with dates

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table with auto number primary key, short date, string field, and
numerous interger fields. I am trying to use the date to be able to
manipulate the interger fields acoording entries in the string field. I am
learning this on my own and have little knowledge of SQL and none in vba. Do
have plenty of books. really need some help.
 
what *specifically* are you trying to do with the date field, and what
*specific* problem are you having?

hth
 
Sorry, didnt know how much information was needed. The string field is
limited to only "day" and "night", the integer fields fall under the
headings of room numbers such as 118,119 etc. I need to be able to deduct
the amount entered under day from night on a daily basis to get the night
total as the data is zeroed out at the end of night only. I need to be able
to do this on a daily, weekly, and monthly basis. All the data is entered
under one table.
 
okay, first of all, recommend you re-examine your table design; including
data in fieldnames (Room118, Room119, etc) breaks normalization rules and
will cause you a lot of trouble when you're trying to aggregate the data, as
in the scenario you posted. for more information, see
http://home.att.net/~california.db/tips.html#aTip1.

second, you still haven't explained *how* you're using the *date* field, and
what problem you're encountering. remember that we can't see your database,
and don't know anything about the process you're trying to support with the
database - except what you tell us.

hth
 
My field names are "DateCompleted"(short date), Shift (limited to Day or Night)
Room118, Room118Rejected, Room118Hours, Room119,etc. (integer), I am trying
to identify the amount produced, rejected, and hours utilized in each room
by each shift. I have to subtract the amount entered for Night from Day to
get the actual amounts produced by Night. (counts are from day to night and
then zeroed out
at end of each night). The only way that I could think of to do this is by
using the Date to identify which Day is subtracted from which Night. I have
made a query for Day and one for Night but do not know how to combine them by
using the date so I am subtracting the right day from the right night. I can
reconfigure my table but since the data is entered at the end of day and the
end of night how would I overcome the primary key (autonumber) Sorry for the
book.
 
you should be able to include the DateCompleted field in each query, then
use both queries as the base of a third query, linking the two on the
DateCompleted field, to pull the two sets of records together, and do the
math as calculated fields in the third query.

hth
 
Back
Top