Query to populate a table using a cursor (1 Viewer)

Hi,

I have a table that has 4 fields called TblStaffLeave ( PersonnelNo (PK), LeaveType(Int),StartDate (date), EndDate(date)
Example data look like
PersonnelNo, LeaveType,StartDate,EndDate
1234 , 1 , 1/7/2017, 17/7/2017
4353 , 1 , 15/8/2017, 19/9/2017
1234 , 2 , 13/4/2017, 23/5/2017
.
.
.

I want to use this data to populate a second table called TblStaffRoster, that has 15 columns, the first is the PersonnelNo (int PK) followed by 14 consecutive dates starting from a Monday each column is of type int. So I want to insert the LeaveType from TblStaffLeave into the column with the corresponding dates! So some staff may have all leave in the 14 columns other may have none.

Is there a way to use a cursor as in TSQL ?

I think it can be done using sql or vba code but prefer to do it in sql if possible.

All help appreciated

Gerry

1. There's no cursors in Access.

2. Why do you need this data in a new table?

3. Look into a cross-tab query: https://support.office.com/en-us/ar...cc8-ba60-2cd8484667e8?ui=en-US&rs=en-US&ad=US

to get all the dates between your two dates you need a table of dates, e.g. 365 records, stating with 1st Jan and ending 31st December and for as many years as you require.

You can then create a Cartesian query - which can be changed to a crosstab as plog suggests

The query would be something like

SELECT TblStaffLeave.PersonnelNo, LeaveType, tblDates.cDate
FROM TblStaffLeave, tblDates
WHERE cDate BETWEEN StartDate and EndDate AND cDate BETWEEN [StartofRoster] AND [EndofRoster]

[StartofRoster] AND [EndofRoster] are parameters - no doubt these would be changed to reference controls on a form. Note when changing to a crosstab you will need to predeclare them using the parameters option on the ribbon. PersonnelNo would be row heading, cdate the column heading and Leavetype the value (use first)

If this is going into a form or report, the other thing you will need to do for the crosstab is to calculate a column heading from the cDate value. you are working on 2 weeks so use the datepart function to get the day number, then adjust using things like mod and \ to reduce this down to the numbers 1 to 14 (or 0 to 13 may be easier). Recommend precede this value with something like "D" or "Day" to ensure you don't create a field with a numeric name - you can have what you like in the form/report as a column header.

CJ_London said:

to get all the dates between your two dates you need a table of dates, e.g. 365 records, stating with 1st Jan and ending 31st December and for as many years as you require.

Click to expand...

Instead of a table of dates, use a query with a Cartesian join (AKA Cross Join) on tables for Days, Months and Years.

Days has a field with records 1 to 31, Months has 1 to 12 and Years has whatever years you want to cover.

The query is
Someone posted the idea on this forum previously but I couldn't find the post.

neat idea!