If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Access query help??
Access query help??
I have a table made up of jobs. It is linked to customers for name/address and linked to a schedule table for dates of tasks. The task table is linked to a task description table that gives a full detailed description of what the task entails. As you can tell it is a one to many relationship, one job will have one customer but many tasks. What I am try to do is produce a query or a report using a query where I can list all the jobs that are absent a particular task, like collect down payment. There may already be many tasks listed and completed but someone forgot to collect or book the down payment. Any help is greatly appreciated!!! |
#2
|
|||
|
|||
Access query help??
The Find Unmatched query wizard can help.
Use the table of all tasks against the table of completed tasks, per job. -- Steve Clark, Access MVP FMS, Inc http://www.fmsinc.com/consulting Professional Access Database Repair *FREE* Access Tips: http://www.fmsinc.com/free/tips.html "Ynot" wrote in message ... Access query help?? I have a table made up of jobs. It is linked to customers for name/address and linked to a schedule table for dates of tasks. The task table is linked to a task description table that gives a full detailed description of what the task entails. As you can tell it is a one to many relationship, one job will have one customer but many tasks. What I am try to do is produce a query or a report using a query where I can list all the jobs that are absent a particular task, like collect down payment. There may already be many tasks listed and completed but someone forgot to collect or book the down payment. Any help is greatly appreciated!!! |
#3
|
|||
|
|||
Access query help??
I just looked at the wizard. Thanks but it doesn't give me what I need. It
will give me jobs that are missing some of the many possible tasks but individual jobs may take a full set of different tasks. This would truly be more like which programming jobs are missing specifications, which database jobs are missing schemas, which implementation jobs are missing work agreement, etc.. I haven't been able to figure out from what the wizard gave me a way to take that and make it work for what I need. "[MVP] S.Clark" wrote in message ... The Find Unmatched query wizard can help. Use the table of all tasks against the table of completed tasks, per job. -- Steve Clark, Access MVP FMS, Inc http://www.fmsinc.com/consulting Professional Access Database Repair *FREE* Access Tips: http://www.fmsinc.com/free/tips.html "Ynot" wrote in message ... Access query help?? I have a table made up of jobs. It is linked to customers for name/address and linked to a schedule table for dates of tasks. The task table is linked to a task description table that gives a full detailed description of what the task entails. As you can tell it is a one to many relationship, one job will have one customer but many tasks. What I am try to do is produce a query or a report using a query where I can list all the jobs that are absent a particular task, like collect down payment. There may already be many tasks listed and completed but someone forgot to collect or book the down payment. Any help is greatly appreciated!!! |
#4
|
|||
|
|||
Access query help??
Two query solution.
-- Create a query that shows all jobs that DO have the specific task. With that query and your jobs table you can use the unmatched query wizard to locate all jobs that don't have the specific task. Generic SQL follows that might let you do this all in one query: I don't know your table structure and field names SELECT Jobs.* FROM Jobs LEFT JOIN (SELECT JobTasks.JobID FROM JobTasks WHERE JobTasks.TaskID = "Collect Down Payment") as Q ON Jobs.JobID = Q.JobID WHERE Q.JobID Is Null OR (This is probably slower than the above, but will work if you have table and field names with spaces or special characters) SELECT Jobs.* FROM Jobs WHERE Not Exists (SELECT * FROM JobTasks WHERE JobTasks.JobID = Jobs.JobID AND JobTasks.TaskID = "Collect Down Payment") "Ynot" wrote in message ... I just looked at the wizard. Thanks but it doesn't give me what I need. It will give me jobs that are missing some of the many possible tasks but individual jobs may take a full set of different tasks. This would truly be more like which programming jobs are missing specifications, which database jobs are missing schemas, which implementation jobs are missing work agreement, etc.. I haven't been able to figure out from what the wizard gave me a way to take that and make it work for what I need. "[MVP] S.Clark" wrote in message ... The Find Unmatched query wizard can help. Use the table of all tasks against the table of completed tasks, per job. -- Steve Clark, Access MVP FMS, Inc http://www.fmsinc.com/consulting Professional Access Database Repair *FREE* Access Tips: http://www.fmsinc.com/free/tips.html "Ynot" wrote in message ... Access query help?? I have a table made up of jobs. It is linked to customers for name/address and linked to a schedule table for dates of tasks. The task table is linked to a task description table that gives a full detailed description of what the task entails. As you can tell it is a one to many relationship, one job will have one customer but many tasks. What I am try to do is produce a query or a report using a query where I can list all the jobs that are absent a particular task, like collect down payment. There may already be many tasks listed and completed but someone forgot to collect or book the down payment. Any help is greatly appreciated!!! |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Last Post on the Topic: MS Access vs. Something Else | david epsom dot com dot au | General Discussion | 5 | April 20th, 2006 11:20 AM |
Access 2000 -vs- Access 2003? | Mark | General Discussion | 5 | November 30th, 2004 06:36 AM |
WORD XP mail-merge FAILS using ACCESS Query | SueMackay | Mailmerge | 1 | November 23rd, 2004 01:03 PM |
Big number gives error! | Sara Mellen | Running & Setting Up Queries | 8 | October 11th, 2004 02:48 AM |
Taher | Setting Up & Running Reports | 1 | August 31st, 2004 09:07 PM |