Newbee to Access 2003

[crater]

I am trying to create a "checklist" form. I have a Tables named: TblClients, TblItems, TblDates and a form named FrmChklist. Form Chklist record source is Tblclients. I have an ID for Clients in each table and an ID for Items in the Items Table, and even a DateID in the Date table.
Now what I need to be able to do is 1) show the list of items for each client. This is "static". what needs to change is the date table. IE item 1 for client 1 may have a date of 12/12/11 but client2 may not have that same date for item 1. I can get this to sort of work with a one-to-many relationship but I cannot get the Dates table to show all records in the Items tbl or for each item. IE I may have to enter a date for item 33 today and tomorrow I need to enter a date for item 5, so I need fields available for each item in the Items table. Does this make any sence? I have attached my db so you can get the idea. Open FrmChklist and you'll see the two subforms I'm trying to work with or better suggestions are welcome.

[orange]

Just tried to open your zip, gave me a CRC error?

[crater]

Try this one, not sure what happened

[orange]

You have some structural problems with your tables. You need to get the tables structured to support what you are trying to do. Your tables need to be normalized.

When I see Child1, Child2..., that's a sign of unnormalized data structure.

Can you describe WHAT you are trying to do (what is your business so to speak) in plain English. Forget tables and forms for the moment.

[crater]

You have some structural problems with your tables. You need to get the tables structured to support what you are trying to do. Your tables need to be normalized.

When I see Child1, Child2..., that's a sign of unnormalized data structure.

Can you describe WHAT you are trying to do (what is your business so to speak) in plain English. Forget tables and forms for the moment.

We are a goverment entity the rehabs low to moderate income housing. I am trying to make a client DB that will store file information (name, address, children, finacial info, work specs ect) that currently is all done in "hard copy paper form" The checklist represents paper forms that must be completed as our projects progress. So to say we start with an specialist inspection and end with a building dept final inspection is incorrect. Some of the items we have to send to the state level and will come back at any given time so there really isn't a chronological order to fill out the checklist by. Eventullay It's my thoughts that if a date field is empty the app. will not allow us to close the project, meaning "hey something is missing from your file". Plus the db will be shared by our entire dept. so anyone could access it. So when I'm out in the field and need a phone number, I can call into the secratatry and she could pull up the client by address and tell me the number, instead of pulling the file out and searching though it to find the phone number. Plus our files are locked up, so the secratary doesn't even have access to them unless one of us is around.
Bascially I am trying to create a "Case Management database" that starts with Intake(Active) and can proceed though the entire process of the rehab and end with closing the file (Inactive)
Does this help any?

[orange]

If you are intent on building a case management data base that multiple users will use daily, you will need to get your table structures set up properly.
There are a couple of case management related data models (links below)

The first deals with legal cases and the second is about social workers cases. You may get some ideas from them.

http://www.databaseanswers.org/data_...ment/index.htm
http://www.databaseanswers.org/data_..._mgt/index.htm

Another good resource to help get you oriented to database design is
http://forums.aspfree.com/attachment...achmentid=4712