UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Querry Design Help, Any Version    
 
   
Kamulegeya
post Sep 10 2019, 10:49 AM
Post#1



Posts: 1,857
Joined: 5-September 10
From: Kampala,Uganda The Pearl of Africa


Hello Members.

Here is my business user scenario:

We have over 1000 employees who in course of their executing their duties go to the field to inspect progress of work(We an engineering firm).

When they go to the field, they get an allowance. They normally go in teams and a team leader usually raises a request for allowances memo for all members of the team.
Each team usually have a driver(some one employed to drive vehicles) and there is a complaint that certain team leaders prefer certain drivers and some drivers dont get picked to go to the field.

Here are the tables i made.

tblRequestsForAllowances(RequestID,Date,EmployeeID,Amount,etc)

tblAllowancesPaymentBatch(BatchID,BatchDate,RequestID(FK),etc)

tblAllowancesPaymentBatchDetails(DetailID,BatchID,EmployeeID,NumberOfDays,etc)

tblEmployees(EmployeeID, PositionID,etc).

tblPositions(PositionID, position,etc)

My question is, can i design a query that shows the frequency or number of times a team leader(can be identified by the EmployeeID in the request for allowances table) went with a driver to the field?

I have data of around 3 years(over 30k records).

Any help will be highly appreciated.

Ronald
Go to the top of the page
 
MadPiet
post Sep 10 2019, 10:57 AM
Post#2



Posts: 3,327
Joined: 27-February 09



Do you have a tiny bit of data? Don't need names, just employeeIDs, dates etc.

This should be simple, but without data, I can't see it.
Go to the top of the page
 
arnelgp
post Sep 10 2019, 11:01 AM
Post#3



Posts: 1,424
Joined: 2-April 09
From: somewhere out there...


you can use Total query.
1. but first you need to identify the earliest date of request, and the latest date.

select employeeID,min([date]) as startDate, max([date]) as endDate from tblRequestsForAllowances
group by employeeID;

2. get the total request for 3 years / (endDate - startDate) = avg request/day.

3. multiply by 7 to get #of request per week.

4 multiply by 30 to get #of request per month

and so on...


--------------------
Never stop learning, because life never stops teaching.
Go to the top of the page
 
Kamulegeya
post Sep 10 2019, 01:00 PM
Post#4



Posts: 1,857
Joined: 5-September 10
From: Kampala,Uganda The Pearl of Africa


Hello,

I have attached a sample with some data(in access 2010).
Attached File(s)
Attached File  sample.zip ( 94.91K )Number of downloads: 2
 
Go to the top of the page
 
RJD
post Sep 10 2019, 02:34 PM
Post#5


UtterAccess VIP
Posts: 10,080
Joined: 25-October 10
From: Gulf South USA


Hi: Some questions ...

QUOTE
Each team usually have a driver(some one employed to drive vehicles) and there is a complaint that certain team leaders prefer certain drivers and some drivers don't get picked to go to the field.

Can a single trip have multiple drivers? I noticed in your data there seem to be two drivers for a single "Batch".

Must each trip have an employee who is a Manager?

Which raises another question - I don't see a TripID. I started using the BatchID, but I am not sure this is the same as a unique ID for a Trip.

This is what I have for you so far (see attached). See qryBatchManagerDriverPairings. Note that there are two drivers for Batch 334 and no manager for Batch 333. That aside, does this look like it is going in the right direction for you so far?

HTH
Joe
Attached File(s)
Attached File  UA_Kamulegeya_20190910_Rev1.zip ( 45.22K )Number of downloads: 2
 

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
Kamulegeya
post Sep 10 2019, 06:51 PM
Post#6



Posts: 1,857
Joined: 5-September 10
From: Kampala,Uganda The Pearl of Africa


HI RJD,

QUOTE
Can a single trip have multiple drivers? I noticed in your data there seem to be two drivers for a single "Batch".


The answer is yes. They may have more than one vehicle. In my database, i am not tracking the vehicles used.


QUOTE
Must each trip have an employee who is a Manager?


The answer is no. Any employee can request for a vehicle to go to the field.

QUOTE
Which raises another question - I don't see a TripID. I started using the BatchID, but I am not sure this is the same as a unique ID for a Trip.


Each BatchID is unique and can used be as a "TripID". Each batch payment is for a particular trip to the field though i didn't use that term.

The problem is that i used an "over simplified db". The actual db is on the server. I will extract actual data with only IDs for employees and share (it is 3am local time now).

My query seeks to find out for example,"do employees prefer to move with certain drivers?".

Can i use this data to find out the relationship?. By say finding the number of times driver A appeared on the payment batches with say employee B?

Ronald.
Go to the top of the page
 
RJD
post Sep 10 2019, 08:08 PM
Post#7


UtterAccess VIP
Posts: 10,080
Joined: 25-October 10
From: Gulf South USA


Hi Ronald:

QUOTE
Can i use this data to find out the relationship?. By say finding the number of times driver A appeared on the payment batches with say employee B?

Sure. I think I understand this better now, after your explanation. See Rev2 of your db attached. See qryDriverNonDriverPairCountsByDateSpan. Enter the date span when prompted and see the results of the data you supplied. See if this is what you need.

If you don't need the date span, you can just remove that part.

HTH
Joe
Attached File(s)
Attached File  UA_Kamulegeya_20190910_Rev2.zip ( 29.15K )Number of downloads: 4
 

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
Kamulegeya
post Sep 11 2019, 02:09 AM
Post#8



Posts: 1,857
Joined: 5-September 10
From: Kampala,Uganda The Pearl of Africa


Hello RJD,

Your suggestion is brilliant!

Problem solved.

uarulez2.gif

Ronald.
Go to the top of the page
 
RJD
post Sep 11 2019, 04:26 AM
Post#9


UtterAccess VIP
Posts: 10,080
Joined: 25-October 10
From: Gulf South USA


You are welcome. Glad that worked for you. thumbup.gif

Continued success ...

Regards,
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    15th October 2019 - 09:03 AM