Group# | Emp# | Date | Total Hrs | OT | Exempt Hrs | Total Tasks | Tasks not Completed
6      | 204  | 1/21 | 8         | 0  | 0          | 8           | 0
1      | 101  | 1/21 | 10        | 2  | 0          | 6           | 1
3      | 356  | 1/21 | 12        | 4  | 0          | 10          | 2

quote:

---

The question what is the best way to add and store the data. I was thinking perhaps a table for every subgroup and then using inner-joins on some of the queries. It would be easier to have one table but I think that 400 records added a day by 30 days might be a bit much. For the Access db and as well the queries that would be run.

---

quote:

---

Man, Access is a real database. Your 400 records are really small in size (just look at the data for each record). Structure your database properly, create the adequate indexes, compact the database regularly, and you should have no problems.

---

quote:

---

First, you could set your db up with the following tables:

Groups
-------
GroupID (Autonumber-Primary Key)
GroupName (Text)

SubGroups
---------
SubGroupID (Autonumber-Primary Key)
GroupID (Number)
SubGroupName (Text)

Employees
---------
EmployeeID (Autonumber-Primary Key)
FirstName
LastName
HourlyWage
... (and any other field you might want for that employee)

EmployeeGroups
--------------
EmployeeID (Number-Primary Key)
SubGroupID (Number-Primary Key)

---

quote:

---

Sorry to disagree with you on this one: if an employee only belongs to a single sub-group you can (and you should) use a foreing key in the employee table to relate each record to a record on the subgroup table. Your option only makes sense if an employee can belong to several subgroups simultaneously.

---

quote:

---

So, 12,000 records a month isn't all that much (considering the size of the data)? That's good to know.

---

A quick estimatee for the size of your daily recors table is between 25 and 30 bytes. 12000 of them makes 360000 bytes a month, that is, around 352K a month for the daily records. You can use your DB for several years without a problem .

quote:

---

I guess I would then begin using inner joins for some of the queries that pull data from the multiple tables? Like total OT for all the group?

---

Every query that needs to use data for more than one table will need inner joins. But use Access QBE and it will be automatically done for you, as long as you define relationships for your tables previously...



Edited by - ruirib on 19 February 2002 21:27:51

quote:

---

But use Access QBE and it will be automatically done for you, as long as you define relationships for your tables previously...

---

SELECT tblSubGroups.EmployeeID, tblActivity.OT 
    FROM tblSubGroups, tblActivity
    WHERE tblSubGroups.SubGroupID = 3
        AND tblActivity.ActivityDate = #2/19/02#
        AND tblSubGroups.EmployeeID = tblActivity.EmployeeID

quote:

---

I then create relationships between the tables using the foreing keys, such as:

Group Table
GroupID (number-Primary Key)
GroupName
any other info

SubGroup Table
SubGroupID (number-Primary Key)
GroupID (Foreign Key with Group Table "GroupID")
Additional Info

Employee Table
EmployeeID (Number-Primary Key)
SubGroupID (Foriegn Key with SubGroup Table "SubGroupID")

DailyActivity Table
ID (Autonumber-Primary Key)
EmployeeID (Foriegn Key with Employee Table "EmployeeID")
All the extra info

quote:

---

But use Access QBE and it will be automatically done for you, as long as you define relationships for your tables previously...

---

I looking for my copy of access to start this up and see exactly what you mean. But what will be done automatically for me? I still need to do the joins on my where clauses, no? such as:

---

In Access in fact you establish relationships by using the relationships window. That's where you tell Access that, for example, GroupID in group table has a 1 to many relationship with GroupID in subgroup table. To do this you just add both tables to the relationship window and then just drag the GroupID column from the group table over to the GroupID column in the subgroup table. Access will open a dialog window where it will tell that you are creating a relationship (1 to many) and you'll have check boxes to choose Enforce Referential Integrity (you should select this one), cascade update related fields (probably should select this one) and cascade delete related fields (I usually leave this one clear in order to avoid undesired "undirect" deletes).

DO this for all the tables and fields where you have foreign keys.

Then, when you create a query, using the QBE, you just need to add the desired tables to the query and Access will automatically create the joins clauses for you. You just need to select the columns you want in the lower area of the QBE window...

quote:

---

OT FOR A SUBGROUP:

SELECT TABLE_DAILYACTIVITY.OT, TABLE_SUBGROUP.NAME
FROM TABLE_EMPLOYEE INNER JOIN TABLE_SUBGROUP ON TABLE_EMPLOYEE.SUBGROUPID = TABLE_SUBGROUP.SUBGROUPID
WHERE SUBGROUPID = 3

Also, if I wanted to do it for the group, do I inner join again on the GROUPID?

---

There is something missing in this query, something that is not missing in Nichols posterior post, and that is the join between the employee and subgroup tables. The easiest way to do this, if you are not quite that easy with SQL (or even if you are I do this all the time as it is easier ), would be to create a new query, add the tables Employee, DailyActivity and Subgroup tables to the query.
Then drag the desired fields to the bottom part of the QBE: OT and Date from DaylyActivity, Name from SubGroup. If you wanted them you could add conditions to fields (for example if you wanted to know the OT for subgroup 3, you dragg ID field from subgroup table to the bottom part of the QBE and then just write 3 in the criteria field for that column; you probably want to add a criteria for date field in order to specify a given date or date interval).

See that in all of my previous explanation I never mentioned SQL, because Access will create it automatically for you, including the join clauses. This would happen obviouslly if you previously created the relationships between tables as I explained in the first part of my message. It's a one time task and it will save you a lot of work when creating queries using the QBE.

Hope this helps a bit...


Edited by - ruirib on 20 February 2002 05:07:39

quote:

---

can you use queries from the QBE in an ASP web page? Or is this using access as the form and interface?

---

Employee1    Box1    Box2    Box3
Employee2    Box1    Box2    Box3
...

quote:

---

Thats Great!!! I got to work and started messing with access. That QBE SQL view is awesome! I didn't know that was there. I did like you said, created the relationships, made the queries and saw the SQL created, which I can just copy and modify to take values from the querystring or what have you. Thanks, I think this will work great! Not to mention it makes understanding the inner-joins a lot easier. Especially for the more complex queries.

---

Glad you like it . I think the QBE is a good SQL learning tool.

quote:

---

Any ideas on the best way to add the records? every subgroup will add 15 records daily, at the same time. Whats the best way to do it one after the other. I was hoping there might be a way to make a form that would add 15 records at a time. But I can't seem to find anything on the ASP sites.
Nikkol, you had an idea here?

---

I think you can probably create an ASP page with enough controls to acommodate input for all workers in a subgroup, get the data for them from a given user, allow you to verify that all is correct and invoke an action script to write all the data on the database.
You could try to coordinate the action of this script from a higher level script that would invoke it with the subgroup as a parameter and would loop through all the subgroups.

Allow me a question: does it have to be done in ASP? An Access form, with VBA programming, would provide greater control and added flexibility in doing this. Obviously if you'll have several people doing this an ASP solution becomes more atractive. But the coordination between the successive intervening scripts is more complex. Of course you could also have a single script doing this, but that will make it even more a complex script.

Tell me what you think...



Edited by - ruirib on 20 February 2002 10:08:43

quote:

---

Allow me a question: does it have to be done in ASP? An Access form, with VBA programming, would provide greater control and added flexibility in doing this. Obviously if you'll have several people doing this an ASP solution becomes more atractive. But the coordination between the successive intervening scripts is more complex. Of course you could also have a single script doing this, but that will make it even more a complex script.

---