Can I Query Fields From Multiple Records? 1

[tomco22]

Can I build a query that pulls fields from multiple records? My database schedules employees, or groups of employees, work days and off days. A simplified version of the structure of a record is:

[Date](PK), [OnDuty](Yes/No), [Group](FK)

I want my form to look like this. An "X" represents a check box:

[u][b]GROUP NAME[/b][/u]

             Sun   Mon   Tue   Wed   Thu   Fri   Sat
10/10/2004    X     X                 X     X     X
10/17/2004    X     X     X                 X     X
10/24/2004    X     X     X     X     X
10/31/2004                X     X     X     X     X
etc.
etc.
etc.

So therefore my query would have to pull the Yes/No field for seven different dates (records). I tried to build such a query but couldn’t get it to work. I tried making each check box a separate sub-form with it’s own query but Access won’t allow continuous forms with a sub-form. Any ideas?


Tom
MCSD
I Nab Terrorists

 

[PHV]

And what about a CrossTab query playing with Format([Date],"ddd") ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[tomco22]

A CrossTab query won't work in this instance because it produces a read only recordset. I need to change the data on the form described above.

Tom
MCSD
I Nab Terrorists

 

[Terpsfan]

Your best bet is to enter the dates as rows during data entry and maybe have another form that displays the results based off of a crosstab query.

The only other way I can think of doing this is creating an unbound form and use a recordset to select, modify values.

Another consideration is if you could associate your results with a calendar type control.

The problem is that you are having to re-orient your data from how it is structured in the backend table. When you pivot that structure, you lose the ability to update records.

 

[tomco22]

Thank you Omega36. This lets me know I am barking up a non-existent tree. The best solution I have been able to come up with is to redesign the tblDate table to include a field for Sun, Mon, Tue, etc. Each of these fields will be Yes/No. Although my query only pulls dates where weekday = Sun, I will update the underlying table with some code that uses DateAdd when the check box on the form, for say Wed, is changed.

Tom
MCSD
I Nab Terrorists