Results 1 to 5 of 5
  1. #1
    Lounger
    Join Date
    Oct 2001
    Location
    Bristol, Bristol, United Kingdom
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sorting lots of data in a report (Access 2003)

    Once again my apologies up front for my ignorance!

    I have a "contractors details" table in which i store all the basic information of a number of contractors. This gives all their information - including the type of work they do (e.g. construction, plumbing, roofing) and the areas within my company in which they do this work (e.g. Cornwall, Devon, Somerset...etc with other counties). At present, i have a column for each area in my "contractors details" table, so that i can have tick boxes to select the areas upon data entry. A contractor can work in more than one area. The type of work is uniform and selected from combo boxes.

    My problem is there are 14 areas. I need to create one simple report listing by area the contractors available in each area and the work they do. My question is how do i sort the report into each individual area? I do not have a single field to base a sort on. i.e the report will show "Devon, Contractor Work, Contractor Name" and then on the next page "Cornwall, Contractor Work, Contractor Name"...and so on.

    I have a feeling i am either over complicating or missing something very basic! Either way i need help!!

    Many thanks,
    Alastair.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sorting lots of data in a report (Access 2003)

    Instead of storing the areas in multiple fields (columns), you should store the areas in a separate table, with one record for each contractor - area combination. This makes selecting areas and sorting on area much more flexible.

  3. #3
    Lounger
    Join Date
    Oct 2001
    Location
    Bristol, Bristol, United Kingdom
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting lots of data in a report (Access 2003)

    Forgive me once again...a returning novice would best describe my Access ability! I expect I am missing the total obvious and will feel daft when the penny drops. Until then though...

    ...if I have a table for "Area", and I have a table "Contractors", how do I link the two together? There is no common field to join. I don't quite follow what you mean by having "one record for each contractor - area combination". I then don't understand how I would arrange my report come the end... if I had a single field called "Area" I could sort by that, but I could potentially have "Area 1", "Area 2", "Area 3"...etc.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sorting lots of data in a report (Access 2003)

    You'd have tables like this:

    tblContractors:

    <table border=1><td>ContractorID</td><td>Contractor</td><td align=right>1</td><td>Acme Inc</td><td align=right>2</td><td>Builders & Co</td><td align=right>3</td><td>Construction Company</td></table>
    tblAreas:

    <table border=1><td>AreaID</td><td>Area</td><td align=right>1</td><td>North</td><td align=right>2</td><td>East</td><td align=right>3</td><td>South</td><td align=right>4</td><td>West</td></table>
    tblContractorAreas:

    <table border=1><td>ContractorID</td><td>AreaID</td><td align=right>1</td><td align=right>2</td><td align=right>1</td><td align=right>3</td><td align=right>2</td><td align=right>1</td><td align=right>2</td><td align=right>2</td><td align=right>2</td><td align=right>4</td><td align=right>3</td><td align=right>3</td></table>
    The tblContractorAreas table defines where each contractor is active, for example Acme, Inc. is active in the areas East and South.

    The tblContractors and tblContractorAreas tables are linked by ContractorID, and the tblAreas and tblContractorAreas tables by AreaID.
    You can build queries based on these tables and sort them any way you want.

    A structure such as the above is called a many-to-many relationship. See <post#=364,203>post 364,203</post#> for an example of how to handle data entry for a many-to-many relationship with a main form and subform.

  5. #5
    Lounger
    Join Date
    Oct 2001
    Location
    Bristol, Bristol, United Kingdom
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting lots of data in a report (Access 2003)

    Ah ha....there goes that penny dropping! Half of it is knowing what to call the darn things....I now know what to call things when looking at the "help" function!

    Thank you Hans!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •