progress form help

[SoxPats83]

I have a DB with a long list of people with assigned tasks. thoughout the course of the day these tasks get completed. what i would like to do is create a form that will essentially show the progress. each person has an idea, example: 1 - bob, 2 - bill, 3 - bruce, etc. i would like this progress form to have the appearence and display something like the following:

Name Remaining Tasks Remaining Task Value
1 - Bob 5 50
2 - Bill 6 23
3 - Bruce 2 80

once again, help is appreciated

 

[PNGBill]

Have you made a query first to collect the data and do any calculations required ?

Once this done, the form part is quite simple and then you dress up the form to make it presentable but the important data is usually assembled in a query first.

 

[SoxPats83]

Have you made a query first to collect the data and do any calculations required ?

Once this done, the form part is quite simple and then you dress up the form to make it presentable but the important data is usually assembled in a query first.

i will have to create that query now then. that is too bad, queries are my weakest skill in access, i never got the hang of them, but hopefully i can make something happen. thanks.

 

[PNGBill]

Queries are the stage one of most access tasks.
You can use Query design or sql - I like design - very easy to do.

Queries by design view have various types of queries.

Select Queries - these are safe and easy to use as they just select data. They can manipulate results. ie calculate fields so you can see a full name rather then one field have first name and another last name. They can sort data and importantly merge data and use criteria to filter data not wanted but they do not change any data held in the tables.
You also have Update Queries, Make Table Queries, Append Queries, Cross Tab and Delete Queries. These do work for you or if misused can solve all your database problems with the push of a button

Forms and Reports need queries to collect the data they use to give you a view that makes sence.

 

[SoxPats83]

so there is pretty much no way to setup codes with a filter or something pulled ffrom the table, like if [whatever field] is null, display, something like that?

 

[highandwild]

Are you able to post a sample of the data in a 2003 database?

 

[SoxPats83]

Are you able to post a sample of the data in a 2003 database?

unfortunately i am not able to upload the DB. sorry

 

[SoxPats83]

do you believe this would be a parameter query?

 

[PNGBill]

You can make a form that just gets its data from one table or even selects data from more then one table but the code for this is sql ie select[field] from, where etc.
You will maybe solve your problem but if you are still unsure of query work then you may well leave a big gap in your skill.

Can drive a car but unable to fill tank and check tyre pressure.

Trust my ideas are not blown out of the water by the many more experienced forum members but... As your database develops you may need to change some of these forms and reports and this is where you will need to edit some of the sql queries they are based on.

 

[PNGBill]

do you believe this would be a parameter query?

If you want data to be shown like a spreadsheet you can use a crosstab query but if you just want to collect data and then produce a form and or report the a select query is all you need.

Select queries can have any number of tables and other queries as there data source and recently a member stated he had some 1,200 fields in his query.

You often need to do more then one query. Query A will collect certain data and filter, calculate etc and query B or C may use query A as one of it's data sources.

 

[SoxPats83]

You can make a form that just gets its data from one table or even selects data from more then one table but the code for this is sql ie select[field] from, where etc.
You will maybe solve your problem but if you are still unsure of query work then you may well leave a big gap in your skill.

Can drive a car but unable to fill tank and check tyre pressure.

Trust my ideas are not blown out of the water by the many more experienced forum members but... As your database develops you may need to change some of these forms and reports and this is where you will need to edit some of the sql queries they are based on.

so with what you said about select[field], i place the field into a form that is where the task results are inputted. if there is anything entered at all, the task is complete, if the field is empty, the task is still ongoing. i would like my new text box to simply total the amount of incomplete tasks per each person. along with the total value of each task.

 

[PNGBill]

SELECT TblTask.taskID, TblTask.TaskDesc, TblTask.TaskComp
FROM TblTask
WHERE (((TblTask.TaskComp) Is Null));

This sql query will select all taskID and TaskDesc records from TblTask where TaskComp field is null

 

[SoxPats83]

that worked like a charm! thank you very much. now on to the next question. i now have a query that lists all the tasks that remain incomplete. each task has a specific value tied to it. what i want to do is display each person once, how many tasks they have left, and the total value of all incomplete tasks.

possible?

 

[PNGBill]

For this you will use group and sum in your query.

I will try and get a sample to you in 5 mins

 

[SoxPats83]

For this you will use group and sum in your query.

I will try and get a sample to you in 5 mins

awesome! thanks!

 

[PNGBill]

Can Bob have task A and task C and Fred task A and task D or will tasks only ever be assigned to either Bob or Fred but to both ?

You could have a TaskTable, PersonTable and TaskAssignTable.

This will allow you to assign task A to Bob as record TaskAssign01
and task B to Fred as record TaskAssign02 and Task A to Jack as record TaskAssign03

Each of these task assign records will always be unique records so long as you refer to the taskassignID number (TaskAssign01 or 02)
If task is "make coffee" then today you assign it to Bob and tomorrow you assign it to Fred.

I will assume this is the way for my sample queries.

 

[boblarson]

Better NOT have Bob make coffee. It will turn out very bad, because Bob doesn't drink coffee

 

[SoxPats83]

in my DB, not single task will be assigned to two people. the table i am querying has the taskID, the person, and the values in it.

 

[PNGBill]

Sorry, already working on ability to assign same task to diff persons but you can still just give task a to Bob, assuming not to make coffee

This qry will display all tasks assigned and there result

SELECT TableTaskAssign.TaskAssignID, TblTask.TaskDesc, TablePerson.Name, TblTask.TaskValue, TableTaskAssign.Date
FROM TablePerson RIGHT JOIN (TblTask RIGHT JOIN TableTaskAssign ON TblTask.taskID = TableTaskAssign.TaskID) ON TablePerson.PersonID = TableTaskAssign.PersonID;

 

[PNGBill]

This qry will give you the tasks with null date (not completed) and sum the values. also give you the number of tasks each person has yet to complete.
I will give details of the tables I used.

SELECT QryTask01.Name, QryTask01.TaskDesc, Sum(QryTask01.TaskValue) AS SumOfTaskValue, QryTask01.Date, Count(QryTask01.TaskDesc) AS CountOfTaskDesc
FROM QryTask01
GROUP BY QryTask01.Name, QryTask01.TaskDesc, QryTask01.Date
HAVING (((QryTask01.Date) Is Null));