Need help pulling data from Access database

[tlmm]

I have an access database from which I am pulling records. The main table contains all the info for a specific person and their business. I am retrieving the data and sorting based on last name, and I am also retrieving and sorting the data based on the specific region a person is in. What I would like to do is also sort based on a sector that a person’s business is a part of. I would be able to retrieve all the records for each sector and sort them just as I did with region, but I don’t want to list ALL the sub-sectors.

Example of sectors:

Education
Health and Wellness - Massage Therapy
Health and Wellness - Medical / Pharmaceutical
Health and Wellness - Physiotherapy
Home Improvement/Construction
Hospitality - Accommodations
Hospitality - Tourism

I would like to just sort by the sectors, not the sub-sectors listed after the dashes. In the example above, I would like to have the headings Education, Health and Wellness, Home Improvement/Construction, and Hospitality with all people in those specific sectors and well as the corresponding sub-sectors listed. So for Health and Wellness, for example, I want to get the records in all the sub-sectors (Massage Therapy, Medical / Pharmaceutical, Physiotherapy), but I don’t want the records to be divided into these sub sectors. I want all the records that fall into each of these sub-sectors to be listed under the heading “Health and Wellness” and sorted alphabetically.

Is this possible and can someone help?

Thanks in advance.

 

[bslintx]

please post code

 

[PaulBricker]

So suggestions would be to put your table data into a query and create a couple new fields.

MySector:Mid([SectorField],1,Instr(1,[SectorField],"-")-1)

should return values like Education, Health and Wellness etc. Then another field

MySub-Sector:Mid([SectorField],Instr(1,[SectorField],"-")+1)

and you should get the sub-sectors listed. Once you have the data in two fields, you may be able to sort/group the way you need. I'm not entirely clear on how you want to sort them because at one point you say you "do not want to list all the sub-sectors" and at another point you say you want to sort them alphabetically.
Anyway, think about it and see if it helps get things in the form you need. If not, let us know and we'll see what else we can come up with.

Paul


Paul

 

[tlmm]

Thanks. I will have a look at your suggestion.

To clarify my first post - I want to list only the sectors, not the sub-sectors, but I want to get all the records from the each sub-sector as well.

For example, if there are 5 records with the subsector Health and Wellness - Massage Therapy, 4 with the subsector Health and Wellness - Medical / Pharmaceutical, and 1 with Health and Wellness - Physiotherapy, then I would like to have the header "Health and Wellness" with these 10 records listed alpabetically by the person's last name.

Thanks again.

 

[PaulBricker]

You might want to set up a report in Access that groups everything the way you want, then Export it as an HTML page and save it to your root directory. That might ultimately give you the best looking document, but would make the data static unless automating the Export.
Just somemore food for thought.

Paul