Help needed with difficult query

M

mscertified

I have this difficult query to write. I know it will end up as a crosstab
query but its way too complex for the wizard. 5 tables are involved:
tblPackages, tblPackageTypes, tblPackageDateTypes, tblPackageTypeDates,
tblPackageDates.
Ok, here goes....
Each entry in tblPackages has a type defined in tblPackageTypes. Each entry
in tblPackageTypes has a set of dates defined in tblPackageTypeDates. The
total number of date types (including the descriptions) is in
tblPackageDateTypes. As the dates are set for the packages, the actual dates
are stored in tblPackageDates. This table may have entries for 0 or more of
the dates defined in tblPackageDateTypes, since not all of the applicable
dates may have been set at any given time.
I hope you are following...........
I need a query to list every package (rows) and every date type (columns)
and have those dates that have been set filled in. In other words I need
columns for every date, not just those in tblPackageDates.
Anyone have any ideas on approaching this?
I'd like to end up with a query that lists the packages (repeating) and
dates and then use the wizard to build the final crosstab query.
 
M

[MVP] S.Clark

It is not uncommon to have the need to create one or more queries to be the
basis of the final crosstab query. In your case, it sounds like you'll need
an outer join to get ALL of the date types. So, make this query, save it,
then try to make the crosstab query from it.


--
Steve Clark, Access MVP
FMS, Inc.
Call us for all of your Access Development Needs!
1-888-220-6234
[email protected]
www.fmsinc.com/consulting
 
Top