Queries, SQl and VB oh MY! Writing a script help

  • Thread starter Thread starter macgyver
  • Start date Start date
M

macgyver

Hey everyone, looking for a little help with access/VB

Here's the situation.....
I have a database that tracks what people in my organization are
working on. The user enters their name and several other generic
things....then enters a number of "tasks" they did in a day, up to 6.
With each task, a release number, segment number(Which is a way to
break down the release) and hours worked associated with that release
and seg number, so for example we have...

release1
segnum1
hrs1

up to 6

What I am attempting to do is make a query then a report, via a table
that will query all six tasks for a particular release.

Here's the part that gets sticky....

If that Release is there, I need to take the hrs that are associated
with that task and add them to a running total of segment numbers for
that particular release.

In other words, today I worked on 4 tasks

task1 would be as follows
hrs = 2
Release = Mac
segnum = Design

task2,
hrs = 3
Release = MAC
segnum = Fabrication

task3,
hrs = 4
Release = Mac
segnum = Design



So what I need the query/vb script to do is say I'm looking for any
record associated with "Mac" So it finds "Mac" and says ok....well
there are two records, then it totals the time and segment numbers and
says on the Release "Mac" there was a total of 6 hrs spent on design
and then 3 on Fabrication.

Does anyone follow me?

I have worked with C and C++ and I can beat my way through VB but I
don't know the syntax that well and I don't know the variable prefixes
to make the form/access relations work. Don't even know if I should be
using something like SQL...

I'm really hoping for some help.....anything would greatly
appreciated.....

Thanks
 
macgyver said:
I have a database that tracks what people in my organization are
working on. The user enters their name and several other generic
things....then enters a number of "tasks" they did in a day, up to 6.
With each task, a release number, segment number(Which is a way to
break down the release) and hours worked associated with that release
and seg number, so for example we have...

release1
segnum1
hrs1

up to 6

What I am attempting to do is make a query then a report, via a table
that will query all six tasks for a particular release.

Here's the part that gets sticky....

If that Release is there, I need to take the hrs that are associated
with that task and add them to a running total of segment numbers for
that particular release.

In other words, today I worked on 4 tasks

task1 would be as follows
hrs = 2
Release = Mac
segnum = Design

task2,
hrs = 3
Release = MAC
segnum = Fabrication

task3,
hrs = 4
Release = Mac
segnum = Design



So what I need the query/vb script to do is say I'm looking for any
record associated with "Mac" So it finds "Mac" and says ok....well
there are two records, then it totals the time and segment numbers and
says on the Release "Mac" there was a total of 6 hrs spent on design
and then 3 on Fabrication.

Does anyone follow me?

I have worked with C and C++ and I can beat my way through VB but I
don't know the syntax that well and I don't know the variable prefixes
to make the form/access relations work. Don't even know if I should be
using something like SQL...
Click to expand...


It sounds like you're new to relational database structures.
I say that because it is a big data normalization No-No to
have records with fields like Hrs1, Hrs2, . . .

The way you seem to have designed the data structure, it is
a nightmare to do the type of calculation you want to do.

Making the weak assumption that there is no relationship
between releases and segments, your table structure should
be more like:

Table: People
Fields: PersonID AutoNumber PrimaryKey
FirstName Text
. . .

Table: Releases
Fields: ReleaseID AutoNumber PrimaryKey
Relname String
. . .

Table: Segments
Fields: SegmentID AutoNumber PrimaryKey
Segname String
. . .

Table: Tasks
Fields: TaskID AutoNumber PrimaryKey
PerID Long Foreign Key
RelID Long Foreign Key
SegID Long Foreign Key
TaskDate DateTime
Hours Int(??)

With this kind of data structure, your calculation can be
taken care of by a single SQL statment along the lines of
this simplified air code:

SELECT Releases.Relname,
Segments.Segname,
Sum(Tasks.Hours)
FROM (Tasks
INNER JOIN Releases
ON Tasks.RelID = Releases.ReleaseID)
INNER JOIN Segments
ON Tasks.SegID = Segments.SegmentID
GROUP By Releases.ReleaseID, Segments.SegmentID
WHERE Tasks.TaskDate = [Enter Task Date]

If you want to present a summary in a report, it would be
relatively straightforward to present the totals by person,
release or segment with subtotals for the other categories.

Your data entry setup would consist of a main form with a
combo box to select the person from the People table and a
continuous subform for the tasks. The subform would have a
combo box for them to select the release and a second combo
box to select the segment along with a text box for the
hours and aonther text box for the date (defaulted to
Date()).
 
Back
Top