display all dates between two dates

[KenThompson]

I apologize if this is on the forum already, I couldn't find anything but may just be searching on the wrong terms.

I have no meaningful experience with Access. I've created a Team PTO calendar on a sharepoint site and am trying to workout getting the views my mgr wants. Which is a matrix of Team members across the top with taken days down the side with each team member taking that day marked on the row.

The data has "starting date" and "ending date" for each record (if it's one day off then they are the same), and each record is an instance of PTO by a team member.
I have the matrix worked out, but only for the dates listed as starting dates. And I could add the ending dates, but that misses the point. I need a list of all days that will be taken. My thought is to replace the "starting date" in the report with a query that is basically
">= starting date AND <= ending date".



2 questions:
Am I trying to do this the right way?
And if so, what is the synatx to get a query like this? I've been reading up on it but can't make anything work. Trying to create a calculated field resulting from the above mentioned formula, but can't get it right.

Thanks in Advance for your help
Ken

[thhui]

Could you give a sample look on your database in mdb format?

[KenThompson]

I'd be happy too, are there directions on how to do that somewhere here on the forum? I don't see any stickys on the main view or anything?

Is very simple, one table
ID | Title (Member Initials) | StartTime | EndTime | Type

That's probably not what you want? If you'd point me to some directions to create what you're asking for I'd be happy to do it.

[thhui]

Do you just concern your starttime?

Is the logic of the query as follows:-

starttime
>= starting date (you want to enter it as parameter)
AND <= ending date (you want to enter it as parameter too)

[KenThompson]

Well, I've tried several ways but I can't display anything in a tabular fashion on here... so I don't know how to make this make sense. The following was my final attempt to show the data but I don't know how to get this forum to show table, can't get spaces to hold their space for freeform... sorry. If you paste each of these html box contents into a notepad doc and name it .html and open it you can see what I mean.

No, I'm not explaining this correctly. What I have is a CrossTab query that looks like this on output:

<TABLE> 
<tr>
<td></td>
<td>Emp1</td>
<td>Emp2</td>
<td>Emp3</td>
<tr>
 
<tr>
<td>3/1/11</td>
<td>1</td>
<td></td>
<td>1</td>
<tr>
 
<tr>
<td>3/17/11</td>
<td></td>
<td>1</td>
<td></td>
<tr>
 
<tr>
<td>4/01/11</td>
<td></td>
<td></td>
<td>1</td>
<tr>
</TABLE>
 

Which is showing:
both Emp1 and Emp3 have 3/1/11 off (this functionality is the whole point to the exercise)
Emp2 has 3/17/11 off
Emp3 has 4/01/11 off

The problem I am trying to solve is that the dates shown are start dates. In actuality Emp2 has startdate of 3/17/11 thru endadate of 3/19/11 off. I have the end date available to work with but don't know how to display:

<TABLE> 
<tr>
<td></td>
<td>Emp1</td>
<td>Emp2</td>
<td>Emp3</td>
<tr>
 
<tr>
<td>3/1/11</td>
<td>1</td>
<td></td>
<td>1</td>
<tr>
 
<tr>
<td>3/17/11</td>
<td></td>
<td>1</td>
<td></td>
<tr>
 
<tr>
<td>3/18/11</td>
<td></td>
<td>1</td>
<td></td>
<tr>
 
 
<tr>
<td>3/19/11</td>
<td></td>
<td>1</td>
<td></td>
<tr>
 
<tr>
<td>4/01/11</td>
<td></td>
<td></td>
<td>1</td>
<tr>
</TABLE>

That's what I am trying to achieve. To have all the dates between startdate and enddate to display as row headings for the crosstab.

Hopefully that is clearer? Thanks for your help and your patience!

[ajetrumpet]

Wow Ken, that might be a little difficult. By the way, you can use the table tags here to paste an actual dataset that's been copied to the clipboard via excel or access. The tags are needed though, I think.

As far as your data is concerned, what you showed for a start date and end date is still the number "1", but both dates as separate records. Are you sure that's right? Why would it be that way, as a format? Doesn't make sense...

If you want a list of dates to be taken off, BY employee, that might impossible, or too complex to use time on, from the setup you've given...

[KenThompson]

Thanks for your response.

I tried copy/paste into forum with and without table tags and it still will only give me a single column table.

The reason for the "1"s is in the cross tab query wizard I chose start time for row headers and member name (actually called title) for column header. And it asks "What number do you want calculated for each row and column intersection". I can choose from the fields not used for headings and then for that field functions like count, first, last, min, max. So I chose type (which holds values like "PTO", "Holiday") and count. So the 1 signifies that the team member for that column has 1 type of day off that day. Logically I'd want something like an "X" just to show on a given day Harry and Sally are off, or on a given day only Harry is off....

All I really want to do is change the row header "start time" with "Date: >= Start Time AND <= End Time" (instead of a list of start dates, a list of all the dates included between start and end times)

I really didn't expect this to be something difficult for Access to pull off. I'm thinking I'm gonna forget Access and just pull the sharepoint calendar down into a spreadsheet and write some VBA or VBS to create the view I want. I know how to do that at least.

[thhui]

It seems that there is missing data in the acccess table if
your usage of start date and end date is a valid description.

eg.

"date", "emp", "dayoff"
3/18/11,"Emp2," 1

Just using normal sql query would not give you what you want.

By using vba, you might need to devise code to add the missing data to the database table first
(applying the logic of start date and end date to add all missing between dates data).
Then, after the above procedures, you could use crosstab query to give you result.

[KenThompson]

Ok, thanks for your help.