Solved

Query design, crosstab

Posted on 2013-02-03
12
285 Views
Last Modified: 2014-02-14
Hello Experts,

Here comes a tricky one. I need to make a query to list tied agents and their budget factor for each sales day throughout a year. There is 100 sales agents and about 200 sales dates (per year).

Ideally, I would like a solution. But good help on where and how to start would be appreciated.

One solution for me would be, to simply make a lot of nested queries. But my final solution needs to perform well also, so tips on how to do this the most efficient way is really appreciated!

Background information

The starting point is that each tied agent has a budget factor on 1 each day. Then reductions should be pulled off, and the final result should be a list of all days for all tied agents with a budget factor per tied agent per day. All of this should be calculated for a time period, typically a year or a month.

The following is a short explanation of the tables and a specification of fields (only relevant fields are included)

One table contains every date with a field specifying a budget reduction percentage for that date. For example weekends and holidays etc. have a weight on 0%, and regular sales days have a weight on 100%. The values are stored as 0-1 values (actual percentage).

tblSalesDates
- intSalesDatePK
- dtmDate
- dblWeightPct

Another table holds information about the tied agent, and the period for which he or she is "active" as tied agent (date-interval). This is relevant regarding budget allocation, since we only want to allocate budget to this tied agent in his/her active period. The tied agent has a foreign key to a employee table, this is because an employee can in theory have more than one tied agent relations during a lifetime, i.e. if he or she changes department, then there will be a tied agent relation to each of the departments at two different time periods. These time periods can never overlap. "dtmActiveTo" can be null (it mostly is), and in that case, the tied agent is active ongoing.

tblTiedAgents
- intTiedAgentPK
- intEmployeeFK
- dtmActiveFrom
- dtmActiveTo

A third table contains information about each employees holidays, school etc., where the employee is having no budget allocated. Each record holds a date-interval, for which, the employees budget should be completely removed (or reduced to 0).

tblEmployeeHolidays
- intEmployeeHolidayPK
- intEmployeeFK
- dtmStart
- dtmEnd

A fourth table contains other budget reductions related to a tied agent, i.e. if the tied agent is only working part-time etc. Each record consists of a reduction percentage in a date interval, and a priority. Priority is because these records can have overlapping time intervals, so the calculation should start by reducing budget with priority 1 reduction first, and 2 thereafter etc. The values in the dblReductionPct are stored as 0-1 values (actual percentage). Also here, the "dtmEnd" field can be null. And as before, in that case, the reduction is ongoing.

tblBudgetReductions
- intBudgetReductionPK
- dtmStart
- dtmEnd
- dblReductionPct
- intPriority

As said, the result should be a list of all sales days (0% weight days can be removed at the beginning) for all tied agents with the final budget factor for each tied agent each sales day.

For what purpose?

By this information it is afterwards possible to allocate X amount of dollars as yearly/monthly/weekly budget etc. to each tied agent each sales date, depending on the budget factors.

I'm using Access 2007 in a split backend/frontend environment with all data in the backend.

Thanks for any, any help!!!

Best regards,

Raahaugen
0
Comment
Question by:Raahaugen
  • 6
  • 2
  • 2
  • +1
12 Comments
 

Author Comment

by:Raahaugen
ID: 38848872
An update,

Ok so now I'm at the point that I have listed a row for each sales date for each tied agent. So the list is limited to the active sales dates (weight is not 0%) and only dates for each tied agent that is within the active date interval.

So this is the resulting query fields:

- intEmployeeFK
- intTiedAgentPK
- dtmDate
- dblWeightPct --> translates to budgetfactor

So for example, if tied agent A has an active period going from 01.01.13 to 01.02.13, this tied agent appears on a row for each sales date in this interval.

This accumulates to 23.246 rows, fyi.

Now I need to take out all holidays, which are stored as date intervals in the tblEmployeeHolidays.

At this point I am stocked!

Please help!
0
 

Author Comment

by:Raahaugen
ID: 38848948
Ok I managed to do this myself by joining the holiday table to the above mentioned, and then validate each row by having a field return true if the date is before the holiday begin OR the date is after the holiday end. Then set criteria for that field. Bingo!

The rest I will manage to do with no big effort.

Still, if there are any good suggestions for improvement and the like, please let me know!

Otherwise I will close the question after a while.

Best regards
0
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 38849885
Raahaugen, the reason you are not getting answers is because Experts-Exchange is about providing expert help to specific technical problems, with very big stress being on "specific". That is,  some difficult error message, some tricky query, sample code that demonstrates how to code a tricky logic, and so on - and it resolves them by collective brainstorming of several Experts, who however don't work as a team, but each gives the advise individually. You, however, have described basically a technical assignment of a project. For something like this, the best approach is to hire someone competent. The main reason is not even the fact that this is too much, but rather that the project requires dedication rather than brainstorming of several independent individuals (sometimes conflicting). Many experts have their contact information in their profiles, and will be happy to help.
0
 
LVL 51

Accepted Solution

by:
Mark Wills earned 100 total points
ID: 38851009
@vadimrapp1, true, but there are some around that enjoy the "long project" :)

So...

The way I would be approaching is starting with a cross join. Trouble is Access 2007 doesnt explicitly have a cross join, so, you need to mention both tables without a join or linking columns.

Trouble with these types of joins is they can very quickly and easily produce a mountain of data and can be slow. So, we need to remember that from the get go...

For you, you need all the dates by tied agent. You have two tables that will give you that

tblTiedAgents and tblSalesDates.

Might even be tempted to make that it's own query and simply make sure you qualify your periods to help control the volume.

You can also restrict the applicable dates to coincide with the Agent start and end

From that relationship, you can then further reduce by a left join to tblEmployeeHolidays using the employeeFK and select where intEmployeeHolidayPK is NULL (ie not on holidays)

Now the final bit, is to join to the budgets. But there are priorities there, so we really only want the top 1 priority for that agent / date combo.

For that, I would likely create a function to lookup any applicable budget restrictions.hat essentially becomes a correlated subquery, and again, not a great position to be in.

So, I am not too sure that you have a lot of options as to what will work best. The relationships first and foremost suggest an approach and unfortunately, you have a lot of begin and end dates and can overlap, so, those need to be resolved first (IMO).

One possible alternative is to expand all the different tables that have start and end dates with your tblSalesDates and then join them together.

And back to vadimrapp1's comment, that is where the complexities start coming into play. It is not so much the data structure as much as it is the data itself.

So, if you would like to continue the discussion, a couple of suggestions for you might help the process...

1) post your query (obfuscating sensitive names or extra columns / information that we dont need to know about)
2) ideally, post a spreadsheet of some dummy data that is representative of your circumstances and show the desired results.
3) In a perfect world, we could see an access database. But that too would likely be a copy with sensitive information masked, removed, or otherwise obfuscated - e.g. employee names might become EMP1, EMP2, EMP3 etc...
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38853565
<@vadimrapp1, true, but there are some around that enjoy the "long project" :)>

...as you can see that is not turning out to be the case here...
;-)


This site is designed to be a Q & A Forum.
One question, one answer.

So, please consider narrowing your question here to one specific "question".

Then you can post sequential, individual follow-up questions as you get each preceding question resolved...

;-)

JeffCoachman
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 38853639
*laughing*, after a few comments like that, then it is going to be very difficult to get any attention.

Great job ;)

Besides which, I thought it was only one question, the only thing missing is the query itself so we can comment on the follow up about performance.
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38853908
mark_wills,
;-)

I was not trying to take away from your post...

I only posted that because the OP had not replied yet, and perhaps no one else was interested...
;-)

Certainly, you seem to be interested in this Q as-is, so I will not interfere.
;-)

Jeff
0
 

Author Comment

by:Raahaugen
ID: 38853967
Hi all,

Thank you for every reply. Sorry for my late response. I have been busy, but I have followed each comment as they were posted, and listened. I know, this question was more like a "case", and I also didn't expect any full solutions. Also, as you can see, I managed to solve the question faster than I expected myself.

Now of course my solution may have potential improvement opportunities (I would be surprised if not), and also I have some new questions to apply from my work this far, which I will probably create as follow-up questions, when I get to it.

Before long, I will post my queries for you to make comments. Right now I'm not at that computer, sorry. Looking forward to improving my solution!

Best regards,

Raahaugen
0
 

Author Comment

by:Raahaugen
ID: 38857160
Hi again,

Ok, so now I ran into a real specific problem with a specific question in relation to the initial problem described:

I have a query containing sales dates for every tied agent:

qryTiedagentSalesdates
- DtmSalesdate
- intTiedAgentID

And a table containing holiday intervals:

QryTiedagentHolidays
- intHolidayID
- intTiedagentID
- dtmStart
- dtmEnd

Now the job is to reduce the initial qryTiedagentSalesdates by removing every date for each tied agent, that is within the registered holiday interval.

So I initially did this by doing a left join and remove rows that had a date within the holiday interval. But problems occour when a tied agent have more than one holiday interval --> then the amount of salesdates for that tiedagent doubles, e.g. So he has two salesdates on one day (even if you sometimes whish it to be possible, it isn't!)

This job is urgent, so hopefully someone has a solition!

Best regards
0
 

Assisted Solution

by:Raahaugen
Raahaugen earned 0 total points
ID: 38857371
Ok, I know this is getting a little annoying, but (again) I managed to solve this myself...

So first I made a seperate query cross joining salesdates and TAHolidays, and filtered out every holiday date for every employee.

This new query I then left joined with date = date and employee = employee to the existing qryTiedagentSalesdates, and again by criteria removed every Tiedagent Salesdate, that appears to be a holiday.

Bingo!

I will make a post with all the queries and a little diagram next time.

Best regards
0
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 39845854
boag2000 Posted on 2013-02-04 at 21:00:25ID: 38853565

<@vadimrapp1, true, but there are some around that enjoy the "long project" :)>
...as you can see that is not turning out to be the case here...


...but it didn't finish the turn.  :-(
0
 

Author Closing Comment

by:Raahaugen
ID: 39858541
Hi,

 Cleaning up my unresolved questions..

Your answer was very usefull, although not complete. I try to make more specific questions i. The future! Thankyou for your attention anyway.

/Raahaugen
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now