Form and query

[darth sidious]

I have just been looking at your tables. If the details in table of students is not fictitious you may want to remove the db attachments you have made in this thread. Let me know if I need to do the same.

Data is all fictitious.

 

[darth sidious]

Still struggling with above. Any help would be appreciated immensely.

thanks
darth:banghead:

 

[bob fitz]

Still struggling with above. Any help would be appreciated immensely.

thanks
darth:banghead:

Yes I know how you feel. Should have something for you by tomorrow.

 

[darth sidious]

Yes I know how you feel. Should have something for you by tomorrow.

Cheers Bob you've been a great help.

 

[bob fitz]

OK, sorry it's taken so long, but I got a bit carried away.
Take a look at the attached db. I have created a new form for the user to assign students to character roles and production jobs. It's not quite working as I would have liked, but see what you think. It uses calculations rather than figures stored in a table.

 

[darth sidious]

OK, sorry it's taken so long, but I got a bit carried away.
Take a look at the attached db. I have created a new form for the user to assign students to character roles and production jobs. It's not quite working as I would have liked, but see what you think. It uses calculations rather than figures stored in a table.

Hi
Thanks for the info much appreciated. Once a student has been assigned to a role/production job the 'got job in production' needs to updated to yes. This doesnt work when a character role is assigned.

Also, I would appreciate a some screen shots with notes in word if possible as I have no idea how you did this and would like to learn it so I can benfit fromit in future. Is there anyway without having to write SQL as I want to keep it as simple as possible. I'm not too bothered about efficiency at this stage.

Many thanks for your help.

Darth

 

[bob fitz]

Thanks for the info much appreciated. Once a student has been assigned to a role/production job the 'got job in production' needs to updated to yes. This doesnt work when a character role is assigned.

Why does this need to be updated.

 

[darth sidious]

Why does this need to be updated.

To indicate the student has a a job assigned as a character or production job.

 

[bob fitz]

To indicate the student has a a job assigned as a character or production job.

If a student is assigned a production job that is recorded in the AssignedJobs table and character assignments are shown in the AssignedCharacters.

 

[darth sidious]

If a student is assigned a production job that is recorded in the AssignedJobs table and character assignments are shown in the AssignedCharacters.

Yes, then the gotjobinproduction in tje student table should be updated to yes.

 

[bob fitz]

Yes, then the gotjobinproduction in tje student table should be updated to yes.

Sorry, but I don't agree. The data is already in the other tables. IMHO, there's no need to duplicate it.

 

[darth sidious]

Sorry, but I don't agree. The data is already in the other tables. IMHO, there's no need to duplicate it.

It is so that the character table can be searched and list of all students returned who have not got a job

ps

can you do a document with notes and screen shots of how you did this. It would be a great help. Also, if can be done without sql that would be better as want to keep it simple. I can then look at making improvements at a later date.

thanks

darth

 

[bob fitz]

It is so that the character table can be searched and list of all students returned who have not got a job

But this can be done in other ways, as it is in the form I created.

can you do a document with notes and screen shots of how you did this. It would be a great help. Also, if can be done without sql that would be better as want to keep it simple. I can then look at making improvements at a later date.

I will attempt this tomorrow.

 

[bob fitz]

I don't find it easy to give detailed explanations but read the following and let me know if/when you need further explanation:-
When the form opens the list box on the right hand side lists all students. Once a production is selected that list only show students who have not been assigned either a character role or a production job in the selected production. The list of available students changes for each production selection. This is done by setting the row source property of the list box to a query statement that returns all the records from the Student table but only records from the query called qryStudAssigned that match. This then has criteria set to only return the records where the Student Number from qryStudAssigned is null. Read about Unmatched queries for more on this. Access provides a wizard to help with this.

Once a production has been selected, the list boxes that show summary details of the production crew and the character assignments are populated. The figures show are calculated at runtime, not stored in a table. Look at the query statements in the row source property of each list box to see how these figures are returned.

Double clicking a student assigns that student to the last selected role/job in the other two list boxes and then removes that student from the list of being available. This is done with some code and by requerying the list boxes. The figures shown in the jobs/roles list boxes change with each assignment.

 

[darth sidious]

I don't find it easy to give detailed explanations but read the following and let me know if/when you need further explanation:-
When the form opens the list box on the right hand side lists all students. Once a production is selected that list only show students who have not been assigned either a character role or a production job in the selected production. The list of available students changes for each production selection. This is done by setting the row source property of the list box to a query statement that returns all the records from the Student table but only records from the query called qryStudAssigned that match. This then has criteria set to only return the records where the Student Number from qryStudAssigned is null. Read about Unmatched queries for more on this. Access provides a wizard to help with this.

Once a production has been selected, the list boxes that show summary details of the production crew and the character assignments are populated. The figures show are calculated at runtime, not stored in a table. Look at the query statements in the row source property of each list box to see how these figures are returned.

Double clicking a student assigns that student to the last selected role/job in the other two list boxes and then removes that student from the list of being available. This is done with some code and by requerying the list boxes. The figures shown in the jobs/roles list boxes change with each assignment.

Hi

Thanks for this. I really appreciate it. The thing is your expert knowledge is far superior to mine and without screen shots and notes i would struggle to do this.

 

[bob fitz]

Hi

Thanks for this. I really appreciate it. The thing is your expert knowledge is far superior to mine and without screen shots and notes i would struggle to do this.

Hi darth,
Thank you for your kind words but, (there nearly always a but ) I have to say that I would not consider myself to be anywhere near the level of "expert", just more knowledgeable and more experienced than your-self, at this point in time.
The benefit of belonging to this forum community is that there is nearly always someone passing by that is prepared to advise and help when one of us is struggling to get to grips with a problem. Anyway, what is it, specifically, that you feel you would struggle to do.

 

[darth sidious]

Hi darth,
Thank you for your kind words but, (there nearly always a but ) I have to say that I would not consider myself to be anywhere near the level of "expert", just more knowledgeable and more experienced than your-self, at this point in time.
The benefit of belonging to this forum community is that there is nearly always someone passing by that is prepared to advise and help when one of us is struggling to get to grips with a problem. Anyway, what is it, specifically, that you feel you would struggle to do.

The new form you made from start to finish that allows character jobs and production jobs to be assigned. Because there are lots of aspects to this form I would need a step by step walkthrough to help me please.

 

[bob fitz]

The new form you made from start to finish that allows character jobs and production jobs to be assigned. Because there are lots of aspects to this form I would need a step by step walkthrough to help me please.

So ask away. What's the first question.

 

[darth sidious]

So ask away. What's the first question.

If we take it a step at a time. Can you first show me how you built the assign character part of the form with any queries associated with it. Screen shots with notes would be great.

 

[bob fitz]

If we take it a step at a time. Can you first show me how you built the assign character part of the form with any queries associated with it. Screen shots with notes would be great.

That is just a list box. Its' row source property is set to an SQL statement:

SELECT Characters.CharacterID, Characters.CharacterName, [NumberNeeded]-Count(AssignedCharacters.CharacterID) AS [Still Rqd], Characters.NumberNeeded AS Needed, Count(AssignedCharacters.CharacterID) AS Assigned, Characters.CharacterGender, [NumberNeeded]-Count(AssignedCharacters.CharacterID) AS a FROM Characters LEFT JOIN AssignedCharacters ON Characters.CharacterID=AssignedCharacters.CharacterID GROUP BY Characters.CharacterID, Characters.CharacterName, Characters.NumberNeeded, Characters.CharacterGender, Characters.ProductionID HAVING (((Characters.ProductionID)=Forms!Form1!cboProdID)) ORDER BY [NumberNeeded]-Count(AssignedCharacters.CharacterID) DESC , [NumberNeeded]-Count(AssignedCharacters.CharacterID) DESC;

This could have been saved as a named query. I used the query design grid to build this statement. You can see what that looks like by opening the form in design view. Open the property sheet. Click the list box to populate the property sheet with its' properties. Look at the row source property and you will see it contains the above statement. On the right hand edge of the property sheet is an ellipse (...) click this and it will show the statement in a query design window, which is what I used. It is based on two tables "Characters" and "AssigndCharacters" which are joined by the CharacterID field which is common to both. When a join (relationship) is first created it creates a join that will return all records where the joined fields are equal. If you right click the join line you can change this so that it returns all records from one table (characters) and only records from the other table that are equal. It has criteria applied to it so that only returns those records where ProductionID field of the "characters" table that are equal to the combo box on the new form are the same. That's why the list box does not populate until a selection is made in the combo box.
This query is used to calculate the figures shown in the list box. To do this click the "Totals" button on the toolbar, which will insert another row (labeled Totals) in the lower part of the design grid and initially sets each column to "GroupBy". You may want to take a look at “Total Query” in the help files.
Some of the columns have been dragged onto the grid from the tables. Some are calculated fields eg: "Assigned:CharacterID". This has had "GroupBy" changed to "Count", so this column will return a count of the “CharacterID” field, and show that in a column called “Assigned”.
The third column is another calculated field. It has been change from “GroupBy” to “Expression”. It will be called “Still Rqd” and will use the expression “[NumberNeeded]-Count(AssignedCharacters.CharacterID)”, which means: Count the fields called CharacterID in table AssignedCharacters and then deduct that from field NumberNeeded. The CharacterID field needs to be preceded here with the name of the table that it comes from because it is in both tables. The NumberNeeded field is only in one of the tables so no ambiguity exists. The [ ] brackets are not actually needed in this instance but must be used if a field or table name contains spaces.
The query is sorted to show records in descending order of this calculated field.
I hope this makes it a little clearer. Post back if you need any clarification.
I will try to look in again tomorrow evening.