How to make a small Db

[Guest]

I am trying to make a small database that has 8 areas/colums ....Job# | Date
| Time Started | Time Finish | Travel Time | Klms/Miles | Total Time |
Comments|

One of the problems that i am having is getting the subtraction from Time
Started and Time Finished to show correctly in the Total Time.

If anyone could help me with this rather small project for my piece of mind
i would be greatful and so would the wife and little one (its to help me
get paid on time from my employers)..... I could do it in Excel but really I
still don't know what I am doing.

Kind Regards
Mac

 

[Duane Hookom]

You generally don't want to store a calculated value. You can add an
expression to a query like:
TotalTime: [Time Finish]-[Time Started]

Since you are new, you might want to learn some basics such as naming
conventions. Don't use Date as a field name since it is a function. Most of
us veteran developers don't allow spaces or symbols (# /) in object names.
Tony Toews has some recommendations at
http://www.granite.ab.ca/access/tablefieldnaming.htm.

 

[Guest]

Thanks for that Duane you are right i should learn some more but time isnt
really on my side here.....
I have a Excel spreadsheet/workbook that i would like to convert to Access
but when i try to bring it over i lose my formulas.

can you point me in the right direction of someone that would like to work
through this with me one on one ?

Mac

You generally don't want to store a calculated value. You can add an
expression to a query like:
TotalTime: [Time Finish]-[Time Started]

Since you are new, you might want to learn some basics such as naming
conventions. Don't use Date as a field name since it is a function. Most of
us veteran developers don't allow spaces or symbols (# /) in object names.
Tony Toews has some recommendations at
http://www.granite.ab.ca/access/tablefieldnaming.htm.

--
Duane Hookom
MS Access MVP
--

I am trying to make a small database that has 8 areas/colums ....Job# |
Date
| Time Started | Time Finish | Travel Time | Klms/Miles | Total Time |
Comments|

One of the problems that i am having is getting the subtraction from Time
Started and Time Finished to show correctly in the Total Time.

If anyone could help me with this rather small project for my piece of
mind
i would be greatful and so would the wife and little one (its to help
me
get paid on time from my employers)..... I could do it in Excel but really
I
still don't know what I am doing.

Kind Regards
Mac

 

[Duane Hookom]

You can ask specific question in these news groups for free assistance. I
did provide the expression to use in a query to calculate the total time.

If you are looking to hire someone to help you, I suggest you review these
news groups for a potential contractor. Many of the Access MVPs (and others)
will help you off-line for a fee.

--
Duane Hookom
MS Access MVP
--


"Big Mac(not the burger)" <Big Mac(not the
burger)@discussions.microsoft.com> wrote in message

Thanks for that Duane you are right i should learn some more but time isnt
really on my side here.....
I have a Excel spreadsheet/workbook that i would like to convert to Access
but when i try to bring it over i lose my formulas.

can you point me in the right direction of someone that would like to work
through this with me one on one ?

Mac

You generally don't want to store a calculated value. You can add an
expression to a query like:
TotalTime: [Time Finish]-[Time Started]

Since you are new, you might want to learn some basics such as naming
conventions. Don't use Date as a field name since it is a function. Most
of
us veteran developers don't allow spaces or symbols (# /) in object
names.
Tony Toews has some recommendations at
http://www.granite.ab.ca/access/tablefieldnaming.htm.

--
Duane Hookom
MS Access MVP
--

"Big Mac(not the burger)" <[email protected]>
wrote in message

I am trying to make a small database that has 8 areas/colums ....Job# |
Date
| Time Started | Time Finish | Travel Time | Klms/Miles | Total Time |
Comments|

One of the problems that i am having is getting the subtraction from
Time
Started and Time Finished to show correctly in the Total Time.

If anyone could help me with this rather small project for my piece of
mind
i would be greatful and so would the wife and little one (its to
help
me
get paid on time from my employers)..... I could do it in Excel but
really
I
still don't know what I am doing.

Kind Regards
Mac

 

[John Vinson]

Thanks for that Duane you are right i should learn some more but time isnt
really on my side here.....
I have a Excel spreadsheet/workbook that i would like to convert to Access
but when i try to bring it over i lose my formulas.

Right. Excel is a spreadsheet and uses formulas in cells; Access is a
relational database, and doesn't. THEY ARE DIFFERENT. An Access table
does not and *cannot* contain formulas.

can you point me in the right direction of someone that would like to work
through this with me one on one ?

That would typically involve a paid consulting relationship. See below
for a suggestion to amplify on Duane's:

Mac

You generally don't want to store a calculated value. You can add an
expression to a query like:
TotalTime: [Time Finish]-[Time Started]

I am trying to make a small database that has 8 areas/colums ....Job# |
Date
| Time Started | Time Finish | Travel Time | Klms/Miles | Total Time |
Comments|

Your Table should NOT contain a Total Time field. The field should
simply NOT EXIST.

Instead, store the other fields in your table. I'd suggest renaming
the fields - no blanks, e.g. use TimeStarted; no special characters
such as slashes; decide up front whether you're storing kilometers or
miles, or (as a bad second choice) add a units field to indicate
which.

Once you have the "real" data stored in your table create a new Query
based on the table. In a vacant Field cell type the formula;

TotalTime: DateDiff("n", [TimeStarted], [TimeFinish])

to get the integer minutes between the two Date/Time field values.

You can use an expression as the Control Source of a textbox on a form
or report to show the total time in hh:nn format:

[TotalTime] \ 60 & Format([TotalTime] MOD 60, ":00")

John W. Vinson[MVP]