Wrox Programmer Forums
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old July 23rd, 2007, 10:46 AM
Authorized User
 
Join Date: Jul 2006
Posts: 26
Thanks: 0
Thanked 0 Times in 0 Posts
Default Challenging Query

Hi everyone, here is one of the tougher problems ive had to deal with.

i have a number of fields, called teammember2, teammember3 etc..to teammember12. This is done because on the form, the user is able to select team members in addition to their original selection using a button.

Another field on the form is a "total days worked".

The problem now is adding the total days worked for each record based on the team member

for example:

if record 1 has : john smith and jane smith as team members
and record 2 has: john smith and tom smith as team members

i need the report (query) show the total days worked for john smith as record 1+ record 2. and for jane smith just record 1, tom smith would show total days for record 2.

im sorry for the weird explanation! i will be happy to clarify anything.

 
Old July 24th, 2007, 07:10 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Here is your problem it seems:

You have one table with something like:

tblMyUnNormalizedTable
PKID
Job
teammember1
teammember2
teammember3
teammember4
teammember5
teammember6
teammember7
teammember8
teammember9
teammember10
teammember11
teammember12

This is not a good structure. You should have:

tblJob
JobID

tblEmployee
EmpID

tblJobs_Employee
PKID
JobID
EmpID
DaysWorked

Then this would be an easy issue.

That being said, what is the actual structure of the underlying table(s)? Can John Smith show up as TeamMember1 on one job and TeamMember2 on another and so on in an unnormalized table? Or is this properly normalized where it won't make a difference?

Lemme know.


mmcdonal
 
Old July 24th, 2007, 08:00 AM
Authorized User
 
Join Date: Jul 2006
Posts: 26
Thanks: 0
Thanked 0 Times in 0 Posts
Default

well you hit the nail on the head im afraid. that is the exact structure of my table. and yes john smith can come up where ever the user wishes, as the first, second, third etc team member on any given job.

the days worked are a seperate table, actually a few different tables, based on the products they are working on. for example if they input data for product1, on job1 and product1 on job2, then those values need to be added.

how should i structure my tables to make this possible. if i use ur idea, will the user still be able to select more than one teammember per job?

 
Old July 25th, 2007, 02:01 PM
Authorized User
 
Join Date: Jul 2006
Posts: 26
Thanks: 0
Thanked 0 Times in 0 Posts
Default

alright, i have normalized my database to help smooth things along. however i still cant figure out how to work my query.

i now have a seperate table for "jobs", "team members" , "workdays product1", "workdays product2" etc for all products.

the tables are structured so that for every job the user can input all the team members on the job, and indicate the number of days worked on each product.

how do i now add all the days worked on a product by team member as stated above?

ive tried using DSum, but without any success, as well as stored calculations within a query. any other ideas?

Thanks,
Jonas






Similar Threads
Thread Thread Starter Forum Replies Last Post
A challenging one....Can this be done? SteveJ Dreamweaver (all versions) 5 September 10th, 2004 04:17 PM
Challenging questions... rguru VS.NET 2002/2003 1 May 26th, 2004 04:40 AM
Challenging questions... rguru VB.NET 2002/2003 Basics 2 May 21st, 2004 01:40 PM
Challenging questions... rguru Beginning VB 6 0 May 16th, 2004 06:35 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.