find the same person who done other things..

[Tech]

hey there. I need some help.

I am tryin to create a query, but of course, for some reason, i am having an off day.

I need to know, how do I create a query which will find a person who has done other things in the database???

hmm, hard to explain

in my database, i have people assigned to a job. now, let's say there is person a. person a might have done x amount of jobs. i need to know how i can create a query which can give me ALL the people and the jobs THEY have done.

any ideas?

 

[Tech]

ok, maybe this will clear things up, i need the query to show all other jobs done by the same person. any ideas?

 

[Rich]

Use a totals query to GroupBy

 

[Tech]

huh? how? I am new so plz help me hehe

 

[Tech]

o wait..no no, i didn't mean to count the number of totals done...like what job has been assigned to person a. person b etc...

 

[Tech]

anyone?

basically, I want all job that person a has, all jobs person b has, all jobs person c has etc.... then I am going to create a sub form with this query and if we are on person a's record, in the sub form it will show their jobs...etc...

 

[Dave Eyley]

Start a new Query using the QueryBuilder.

Add the table(s) to the query that hold the person's name and the jobs they've done.

Drag the person fieldname and jobsdone fieldname to the query grid.

Save the query and use it for the source of the subform.
Create the main form and subform and on the main form have a Combo called say, 'Person'.

Make the Combo's RowSource the person filedname from the relevant table.

Open the Property Sheet for the subform, by clicking once on the subform and clicking on the Property Sheet Icon.
In the LinkMasterField - put the person fieldname
In the LinkChildField - put the person fieldname

Now when the form is opened and a person selected from the Combo, the subform should show only the records associated with the selected person.

I would need to know more about your table design before I could be more specific.

HTH

Dave E

 

[Tech]

hey, thanks for that, I REALLY appreciate it. This is what i did, but didn't quite work out.

Now, let's take an example.

in the table, I have person a.

Person a has 4 jobs.


Person b has 3 jobs

now, i created a query, put in the table i needed, dragged the job number, the person and also the jobs field. ran the query, great!!! save it. ok.

now, in the forms, in design view, i made a new subform, clicked on "Use existing tables and queries", then I selected "Query: jobs" and dragged all the fields (3 fields) in to the other list box. Clicked on next.

then i selected "Show Jobs for each record in the table using job number" and clicked next and finished it. ok it works.

however, when we goto person a, it only shows job number 1, but not all 3 jobs in that subform but in the query it has 3 jobs

Same thing with person b

any ideas?

thanks

 

[Dave Eyley]

Have you set the subforms property to either Continuous Forms or Datasheet?

If not, the form can only show one record at a time.

HTH

Dave E

 

[Tech]

It's in Datasheet view, like the other stuff......and the other stuff works fine.

 

[Dave Eyley]

How does the subform know which person's jobs to find?
Is there a field on the main form?

 

[Tech]

well, the job id or job number is a primary key, a UNIQUE field. it's on the sub form also and of course in the main data table

 

[Dave Eyley]

So the job no. is the Primary Key and is a 1 to many relationship with the other table?

I would have thought that in the main table you would have had a primary key of PersonID which would be the 1 to many with the other table.

Then, each Job would have two identifying fields -

1. PersonID to link the person to all of his/her jobs and,

2. JobNo to identify each job uniquely.

If this is the case, then the statement you used -

"Show Jobs for each record in the table using job number"

should read -

"Show Jobs for each record in the table using PersonID"

This would then show all the jobs related to the selected person.

Dave E

 

[Tech]

*gulp*

not having a good day at all!

 

[Dave Eyley]

OK Tech,

I knocked this example together to help explain my method -

I have not created queries and I've used a Combo for the person selection for ease. But it will show you one way to do what you are after.

Look at the RowSource and afterupdate for the combo, and look at the Master/Child properties for the subform.

I really hope this helps... 'cause I gotta go out now - it's Saturday night, you know.

Dave E

 

[Tech]

thanks!

 

[Tech]

yeh, that's the stuff! I tried doing it in a similar way again, without using a query, trying to copy yours but still, it doesn't work for me it just shows the CURRENT job and no other jobs

 

[Dave Eyley]

Can you post the db so that I can give it the once-over?
If it's too big, can you just copy the bits we've discussed into a temp database and post it?

There's got to be something simple going on here...

If you can't do that, study the example again and copy the format exactly. If you tables are set out like mine, then it's got to work.
Hasn't it?

Dave E

 

[Tech]

um, i can't cut the bits out lol..it's all related and stuff...u will understand when isend it.

please contact me on ICQ : 139363222 or msn:

ahmeda86@hotmail.com

thanks btw, i REALLY do appreciate it