Crosstab query to display data on a form?

[RichardMGreen]

Hi all

I've got data stored in an SQL database with an Access 2010 front end (forms, etc) and I'm having a few issues displaying the data as the users want it.

I'm trying to attach a spreadsheet which shows the data storage and required layout but I can't see how to attach a file. I'm also at a loss as how to sort it out the layout issue.

I've had a look at crosstab queries but they don't seem to do what I want.

In a nutshell, the data is stored in a table with one column for each data item (you can ignore the ID field, that's just to ensure we can't have duplicate records).

I need to do some sort of crosstab where the user specifies the start date of the period and the form displays 7 days from that date on the form in the layout that's on the second tab of the attached.

The spreadsheet (second tab) would show 2 dates (to give you an idea of the layout) but I need to show 7.

I'm also having to use a multiple items form to show all the relevant staff members for the dates, etc.

Any help on this would be greatly appreciated.

 

[ranman256]

DONT. Crosstab fields can change constanly so you cant use a form. Just use the spreadsheet.
now, if you knew EVERY possible column outcome, then you can build a form using every one.

 

[RichardMGreen]

I didn't literally mean a crosstab query (which wouldn't be suitable anyway).

I just meant that I need some method of display vertical data horizontally.

For example (this is test data):-

Ward StaffBand StaffName Date          ShiftType ShiftStart ShiftEnd
A4          7       Person1    08/06/2015 EL           07:00      19:30
A4          7       Person1    09/06/2015 EL           07:00      19:30
A4          7       Person1    10/06/2015 EL           07:00      19:30
A4          7       Person1    11/06/2015 EL           07:00      19:30
A4          7       Person1    12/06/2015 EL           07:00      19:30

I need to display this data so that it appears as follows:-

Ward StaffBand StaffName Date1        ShiftType1 ShiftStart1 ShiftEnd1 Date2        ShiftType2 ShiftStart2 ShiftEnd2 
A4         7        Person1    08/06/2015 EL            07:00        19:30      09/06/2015 EL            07:00      19:30

and so on.

Apologies for the formatting.

I've tried running a query with sub-queries to get the right format but that becomes a read-only dataset.

I also need to attached this to a form so it can be updated without exposing the base tables (it will also hopefully speed up data entry).

Has anyone got any ideas?

 

[RichardMGreen]

Anyone any ideas please?

If it helps, the base tables are on an SQL database and are being accessed via linked tables into the database.