Design advice

[Guest]

Hi, please could someone advise me on the best design for an integrated
system I am working on. Currently I have one Itinerary table which holds
Specialist, Activity, StartDate, NoOfDays, DealerCode and other fields which
relate to an audit activity eg auditno, claimcount etc (which are blank if it
is not an audit). I have a crosstab query which produces a weekly calendar
plan for each person.

This works but the users are used to seeing what each person is doing every
day and if an audit lasts, for example, four days there is no entry for them
on the 2nd, 3rd and 4th day. Is there a way to dynamically display the same
data on a report somehow using the NoOfDays field? Or is it best to have one
record per day in the table? The trouble being with the latter is that all
the other audit fields would be duplicated too. An added complication is that
the initial data is written to a temp table from an append query run in an
external database, the user selects the audits they want to book and I want
these records only to be written to the itinerary table.

Sorry if this is unclear, I can provide more info if required.

Thanks in advance for any help.
Sue

 

[Guest]

I also need the holidays displayed on the calendar report. At the moment
these are stored in a seperate holiday table (tblholiday) but they are on a
per country basis rather than individual specialist (Specialist table does
have country code in it though).

Can a report be built up in code to pull data from two tables that are not
linked?

Thanks in advance for any help.
Sue