Noob help please

[cdzajp]

Hi all

I am thinking I need an update query to answer my 1st question and a totals qry for the second question but dont actually know how to do it. Any advice or help would be greatly appreciated.

Question 1
I have 21 fields showing on a form (user request) they are as follows:
Monday On (this is a yes/no button) Monday on time monday off time and total hours this is for 7 days per week.

However what I want is the value from monday (user selects) to replicate in the other fields below it.

Question 2
With the same information above, I want the total hours box to show the total time between Monday on and Monday off - So for example Monday on is 08:00 and off is 16:00 the totals box will show 8 hours.

Can anybody help or advise please.

Thanks

CD

[jzwp11]

How your tables are structured is of critical importance, so before we get to your questions, can you provide more detail as to your table structure (tables, field names and relationships between tables)?

[cdzajp]

First of all, my thanks for your reply. The noob word in my initial post i think should have had big flashing stars around it.

I only have one Table and One Form with table fields as follows:

Site Name / Lights on / Lights Off / Total

Example of how I would like it to work is as follows:

Site Name / Lights on / Lights Off / Total
Test 08:00 16:00 8hrs

I hope that helps

Thanks

[jzwp11]

No problem about the noob, we all have to start somewhere.

I assume you have many sites, so it would be best to put those in a table, 1 record for each site.

tblSites
-pkSiteID primary key, autonumber
-txtSiteName

I assume that for each site you will have many lights on/off events: one(site)-to-many(events) relationship.

tblSiteEvents
-pkSiteEventID primary key, autonumber
-fkSiteID foreign key to tblSites, this needs to be a long number integer field. This field is used to build the relationship between this table and the site table.
-dteEvent (a date field to capture the date of the event)

Technically speaking if you have more than one event (on & off would be 2 events) on a give day, that describes a one-to-many relationship which would be represented in a separate (but related) table according to the rules of normalization.

tblSiteEventDetails
-pkSiteEventDetID primary key, autonumber
-fkSiteEventID foreign key relating back to tblSiteEvent
-Event (on or off)
-tmeEvent (time of the event)

You would not (and should not) store the elapsed (total) time between the events since it can be calculated whenever you need it.

Now if you will only, ever have 1 on and 1 off event per day at a site, some would say that the above, fully normalized structure is overkill. For a less-than-normalized approach, one might have this structure:

tblSites
-pkSiteID primary key, autonumber
-txtSiteName

I assume that for each site you will have many lights on/off events: one(site)-to-many(events) relationship.

tblSiteEvents
-pkSiteEventID primary key, autonumber
-fkSiteID foreign key to tblSites, this needs to be a long number integer
-dteOnEvent (date/time field to capture the on event)
-dteOffEvent (date/time field to capture the off event)

I would typically go for the fully normalized structure, since it will give you the most flexibility in the long term. Without more info about the busniness process you are trying to model, it is difficult to say which approach would be best for you. Could you provide more details of what business process you are modelling?