Help Please. Mixed Subject I think. Possibly a relationship issue, or a query / report issue.

[JGMUSMC]

So I have a database that has a table of volunteers and a list of job sites for those volunteers to work.



I have created a query that takes the info from both tables and the criteria of a Colum to only show people that are assigned to that specific Job site.
The volunteers have a column that has job site assignment, and the job sites have a column that assigns a job number to them.

So for example, my query gives me a list of names of volunteers and the job site that they are on. What I get is a list of each person and the job site information. I want to make a report but, it repeats the job site information over and over for however many people I have assigned for it. I am not sure if I need to make a relationship or join the tables or somehow edit the query to only show the jobsite once. I tried different grouping but it only groups the one piece of info and not all the other fields. I tried using distinct in the criteria for the other fields, but it then just wipes them out completely. I am sorry I can't provide a screen shot as to not violate people's personal information. Any and all help is greatly appreciated. I don't know a whole lot about Access so please treat me as ignorant to the subject and have some mercy. Please ask as many questions as you need for clarification, I will answer them the best that I can.

Thank you

[Welshgasman]

Normally you would have another table, that links volunteers and jobs it's. That would hold extra info like date they started and ended.

[Micron]

Did you try grouping on the site in the report design? You should get a group for each site with all the people that are related to the site. However, a person's name will appear all over the report if they're in multiple sites. You should be able to mock up something in Excel and just copy/paste it here as a table. I find that works best if the text is centered in the cells.

It's possible that you do need a 3rd table as suggested, because you likely have a many to many relationship between people and sites.

[davegri]

Your specs sound like a potential Many-to-Many table situation. Can volunteers be assigned to more than one job site? Can job sites have many volunteers assigned?
Have a look at this many-to-many demo.
Many-To-Many, two ways to update Junction table, includes Not In List handling

[orange]

JGMUSMC,

It would help readers and you if you gave us
a) the 30,000 ft overview of the proposed application in simple terms
b) an example or 2 of volunteers and jobsites.

It sounds like your situation involves the following (3 tables)

Volunteers--->Volunteer works on Jobsite <---Jobsites

but you know your environment and the details.

[JGMUSMC]

JGMUSMC,

It would help readers and you if you gave us
a) the 30,000 ft overview of the proposed application in simple terms
b) an example or 2 of volunteers and jobsites.

It sounds like your situation involves the following (3 tables)

Volunteers--->Volunteer works on Jobsite <---Jobsites

but you know your environment and the details.

Here is an example of the 2 tables that I have. I don't have any relationships setup yet. As I am not sure if I need them yet. Like I said I am fairly ignorant to Access. I know enough to be very basic. The top example is a table with volunteer information. The bottom is an example of job sites. The volunteer can only be assigned to one site per day. So they can't be assigned to multiple jobs for that day. Unless they finish one early obviously but we are not going to worry about that here for this purpose. Again any help is greatly appreciated!!!! Thank you.

Submit Id (Key Value)	First name	Last Name	address	phone number	Skills	Jobs proficient at	Site assignment
12345678	John	Doe	123 Main St. Some Town in some state	1234567890	all	any	1
submit Id (Key Value)	First Name	Last Name	Address	Phone number	Job discription	Supplies	Site Number
12345678	Jane	Smith	214 main st. some town in some state	2345678901	many	all	1

[JGMUSMC]

The goal is to have a report with job site 1 and it's information at the top, and below that a list of all the people that are assigned to that job site. With my current query, I query the info above and set the criteria to Like "*1*" for the site assignment and sight number so I only get the people and that site. But I also end up with it listing the site info each time for each person.

[Welshgasman]

Then do not show the site information in the detail section.
The site information should be in the header, and all the volunteers go in the detail section.

You do not have to show everything you bring in as data to the report.

Also if you want one report per site, then you are going to need to pass that info in the WHERE paramter of the OpenReport command.

[jojowhite]

don't use partial criteria (Like), use 1 as the criteria.

Criteria: 1

[orange]

JGMUSMC,

You could post your tables designs---field names and some sample records. And/or your SQL underlying your query(ies). A graphic of your current report output could be helpful.

I notice you have SKILLS identified in your #3. Do jobsites have specific skill requirements?

[Micron]

You now have two answers that said to create a grouping (header) on the site value. If you're filtering the site via query criteria then what I said about names being all over the report in different site headers won't apply because there will only be one group (site 1). Maybe you should take a look at this, or something similar:
https://support.microsoft.com/en-us/...5-7158dcd3681c

[June7]

Build report with Grouping on site ID. Site info in Group Header and volunteer info in Detail section.

Or don't use Grouping and just use Sorting with all data in Detail Section. Set textbox HideDuplicates property to Yes. The effect will be similar to a Grouped design.

In either case, report can serve for 2 purposes:

1. show all sites and volunteers (no filter criteria)

2. show only one site data (open with filter criteria)