crosstab query

  • Thread starter Thread starter Ron
  • Start date Start date
R

Ron

I need some help with creating a crosstab query. I havent
had any experience with them, so any help will be highly
appreciated. Any links to sites with crosstab examples or
tutorials will be very helpful.

The following is the list of tables which have a 1 to
many relationship.

tblRoom (1)->(many) tblCabinet (1)->(many) tblDrawer (1)->
(many) tblFile (many)<-(1) tblDoclist (many)<-(1)
tblDocType

Other relevant tables relationship are -
tblCabinetType (1)->(many) tblCabinet
tblDrawerType (1)->(many) tblDrawer
tblFileType (1)->(many) tblFile


Structure of relevant tables -

tblCabinet -
intCabID - Autonumber
intCabtypeID
intRoomID
boolLocked - If locked, then it cannot have any other
entries in tblDrawer and onwards.

tblCabinetType -
intCabTypeID - Autonumber
strCabtype

tblDrawer-
intDrawerID - Autonumber
intCabID
intDrawerTypeID

tblDrawerType-
intDrawerTypeID - Autonumber
strDrawerType

tblFile -
intFileID - Autonumber
intDrawerID
intFileTypeID
intDoclistID

tblFileType -
intFileTypeID - Autonumber
strFiletype

tblDoclist -
intDoclistID - Autonumber
intDocTypeID
strDocument

tblDocType -
intDocTypeID - Autonumber
strDocType

I need to create 2 crosstab queries.
First, row headings - values taken from strDrawerType and
column headings - values taken from strFiletype. The
output must show the Count for each value of
strDrawertype which is contained in each strCabType. In
simple terms, i need to know how many Filetypes are
contained in each Cabinet Type.

Second, row headings - values taken from strCabType and
column headings - Count of cabinets of each type; Count
of cabinets which are locked; Count of cabinets which are
unlocked; values taken from strDrawerType. For, values
taken from strDrawerType, the output must show the Count
for each value of strDrawerType which is contained in
each strCabType. In simple terms, i need to know how many
DrawerType's are contained in each Cabinet Type. The
output shd look like -

TypeOfCabinet CountofCabinet CountOfLockedCabinet
CountOfUnlockedCabinet Drawer1 Drawer2
Cabinet1
Cabinet2

Thanks
 
Unfortunately, in the amount of time that it took you to create this post,
you could have created about 10 example crosstab queries on your own.

Start with a simple one by using the Crosstab Query Wizard, and work your
way up from there.


--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
 
Back
Top