How do I create a report showing only the fields with a certain value

[jharpsx]

I have a table, Shows, which is a checklist for the tasks that need to be completed for each show date. In the shows table, there are several different tasks being tracked and each task has 3 fields (Status, Date, Remarks). For instance the following 3 fields track whether or not the contract was received: AContractReceivedStatus (has 3 options: Completed, In Progress, Not Started), AContractReceivedDate, AContractReceivedRemarks.

What I need to do is print a report showing a list of any show that has any task that is either In Progress or Not Started.

Example report with field names:

***
Artist
ShowDate
Building

[If AContractReceivedStatus is not started or in progress, it is displayed]
-ContractRecd: AContractReceivedStatus AContractReceivedDate (AContractReceivedRemarks)
[display other fields if status marked not started or in progress]
***

Let's say that a show on 1/1/2005 is missing its contract and insurance, the report would look like this:

***
Any Band Name
1/1/2005
Any Venue Name

-Contract: Not Started 6/10/04 (not yet received)
-Insurance: In Progress 6/10/04 (waiting on request)
***


Any help from you Access geniuses out there would be much appreciated. Or if you can suggest a better way to track these tasks, please let me know (I'm not opposed to rearranging my database schematic).

Thanks,
John

 

[scriverb]

Are three tasks and their related fields all located in one record with the Show info? If that is correct, then I would suggest normalizing the database. The appropriate structure would be two tables. tblShows and tblTasks

When a new record is created in tblShows with all of the appropriate inforamtion about the show then when that record is initially saved then three records would be added to the tblTasks each with the three fields as you described above. In addition there would be a foreignKey(FK) that would be the Primary Key(PK) value that was created in the tblShows. They would be linked as a one-to-many relationship. Now we can analyze the tblTasks easily with a query and look for records that meet the criteria that you have described and display this in a report very easily. Given you current structure there would be a need to create a UNION query that would split apart your one table into a recordset similar to the design that I described above for the tblTasks.

Now I can help you do both of these but would recommend that you choose the more normalized and accepted approach to the database design.

Let me know what you deceision is and I can help with either.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[jharpsx]

Thanks Bob.

The reason I chose to put all of the tasks into the Shows table as separate fields was to facilitate data entry, and because at the time it seemed like that was the only way to do it effectively.

I think your first scenario is the better choice, I'm just not sure how to set something like that up.

Thanks in advance.

 

[scriverb]

How far are you in your database design. Have you started to enter a bunch of data or are we at the beginning design stages where we can start over?

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[jharpsx]

I have very few records, so revamping is not a big deal.

 

[scriverb]

Before we get started what are the different tasks that are being tracked for each Show. I am just trying to get a handle on the scope. I realize that there are three fields for each task but what are the tasks.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[jharpsx]

Here they are:

- FaxCalendar
- MarketingInvoice
- EventSetup
- Website
- ShowFileCreated
- AContractsReceived
- ADealPtsReviewed
- AContractReturned
- ARiderReviewed
- ARiderReturned
- ADeposit
- AInsurance
- BAgmtReceived
- BAgmtRevised
- BAgmtReturned
- BDeposit
- BInsurance

 

[scriverb]

Okay, let's first modify the table Shows. Add an AutoNumber field if it does not already have one. Name this field RecNumber. Delete all fields that do not relate to just identifying the show, name, date, location, etc. This information is stuff that only needs to be identified once in your database.

Now the second table would be the many side of the relationship.

Table Name: [b]Tasks[/b]
RecNumber    Autonumber   primary Index
LinkToShows   Number       Long Integer
Task          Number       Long Integer
Task_Status   Number       Long Integer
Task_Date     Date/Time    
Task_Comments Memo

Now we need some lookup tables:

Table Name: [b]Tasks_Desc[/b]
RecCounter    AutoNumber   Primary Index
Task_Desc     Text         50

Now you can update your relationship between the table Shows and table Tasks. Create a relationship link between the Shows/RecCounter field and the Tasks/LinkToShows field. This should be a one-to-many relationship, enforce referential integrity. For each Show you can now have many tasks assigned.

Now fill the table Tasks_Desk with all of the Possibile Tasks that you want to track for the shows. This would be the list that you provided in your last posting.

When this is complete post back and we can then move to the next step.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[jharpsx]

I made the tables as specified, created relationships and enforced referential integrity. Ready to proceed....

Thanks

 

[scriverb]

Create a table and name it TaskStatus. Just a couple of fields are necessary:

Table Name:  [b]TaskStatus[/b]
RecCounter             AutoNumber
TaskStatus_Desc        Text             30

Now enter all of the possible TaskStatus's.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[jharpsx]

Done.

 

[scriverb]

Now we want to create a subform form with the table Tasks as the RecordSource. This would be a Continuous Form, vertical scroll bar only, record selector, Form View only, and should include all of the fields in the table. Space them out horizontally but make the controls for the RecCounter smf LinkToShows have a width of 0"(thing line only). Also, set their tab stops to NO, and the visible property to No. Just push them over to the left and move all the controls over and with one on top of them. We don't to see it but we want it to be there for reference if we have to. The controls Tasks and TaskStatus should be comboboxes. Each should have the corresponding table as its RowSource. The Bound Column should be 1 while the columns widths would start with 0" and then appropriates size to view the other value. Name this subform form frmSFTasks.

When you get that done we then want to create your Main for with the table Shows as the RecordSource. Put all of your controls on their that you need to identify the show.

Then we need to create a subform control with the newly created subform form as its ControlSource. The SourceObject for this control should be frmSFTasks. Set the Link Child Field to LinkToShows. Set the Link Master Field to RecCounter.

We are getting close here and so put this together and I then test to see if you can bring up a Show and add Tasks to the subform form.

Post back with questions or problems.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]