dazed confused building tables

[Casmurbax]

Good day,

In looking through the forum I am not finding what i need to do. I am sure the answer is on here, but perhaps I am not wording my search correctly. I apologize in advance if the answer is on here and I don't see it.

My problem is I don't know how exactly to begin.

I want to keep track of 11 rules and that could have 1 of 4 different things (violations, transfers, letter sent, referred out) could happen to each of those rules.

For example rule #1 I could have 10 that no violation occurred, 5 transfers, 1 letter sent and 2 letters sent as seen in the attached picture.


I created tables for each of the following: violations, transfers, letter sent, referred out.

Each of those tables has a fieldname for the 11 rules. How do I link all those tables together?

Hopefully I explained myself and i appreciate any help that I can.

Thank you

 

[Colin@Toyota]

Maybe I am simplifying this too much... but doesn't the example you posted pretty much store and display the data the way you want? If this is the case, I would just use Excel... no need for Access.

 

[Casmurbax]

No you are not simplifying it to much. I will have many users doing this and this form will need to be done every work day and I want to be able to query the information. For example How many rules 1 had no violations for the month of August.

hope that helps

 

[Colin@Toyota]

Ahh I see..

Well, in addition to the 4 occurances for each rule, it looks like you will also want to track the date, and the user that input in the information.

Also, I am curious if on each day something will occur for each rule?... If so, will there be a number going into each of the four fields of each rule every time?

 

[Casmurbax]

hi there thank you for your reply.

yes I will be tracking the users and the date as well the user could possibily have something for each rule everyday.

you could have 4 different things happen for each rule everyday.

John

 

[Colin@Toyota]

Sorry I didnt have much time to spend on this one, but this should be a starting point for you... There are things that need to be done with this one before it could be used, like setting the date as Indexed(No Duplicates), etc.

For what you need, the Access wizard and help menus should be able to guide you through... I think

So I'll tell you what... Get as far as you can modeling your db after this (loosely!), and if you need some help with anything let me know.

 

[Casmurbax]

from my quick review of it I believe I can get this.

thank you very much for your help.

I really appreciate it. !!


John

 

[CraigDolphin]

Cas,

here's an alternative schema to consider.

This dynamically creates the summary table using a start and end date that you can edit, and let's you see individual entries from the same time period. Any new entries use the username of the current user and the current date as defaults. You can either enter outcomes singly, or as batches by changing the quantity field from the default of 1.

Hope it helps.

 

[Colin@Toyota]

Craig,

That db was had way more thought and planning behind it than mine... and it probably took you less than half the time! Cheers for being such a willing and knowledgeable participator on this forum!

Cas,

Good luck with your project!

Colin

 

[CraigDolphin]

Half the time? I dunno about that...I'm just a biologist who dabbles with this stuff.

I'm happy to help where I can. It's only fair payback for all the incredible help and education I've benefited from here.

Besides, IME the best way to learn something is to teach it or practice it Helping others benefits me by forcing me to learn new things (like this db forced me to learn how to retrieve the network username using vba. I might not use that knowledge now...but someday I'm sure I will) My own access work has improved out of sight since I became a regular on this forum. Still got a ways to go though

 

[Casmurbax]

Craig,

Just dabble? What Colin did I thought was more than enough. You went over the top; I can not thank you both for your help. I really do appreciate it from the both of you.

Thank you very much Craig. More than I could ever wanted.

Colin, I appreciate what you did for me as well.

Thank you again.

John

 

[CraigDolphin]

Hope it's useful. Post back if you have any questions or problems with it.

 

[Casmurbax]

Craig,

Again thank you for what you did earlier. I appreciate it.

I been playing around with it and having people try it out.

The people that have used it can not understand the concept of filling the information on the sub form (frmEvents) of the fmMenu form and then seeing the results on Query3 sub form. Is there an easy way to have them just fill in the information using a form much like the query3 sub form? Listing all 4 outcomes?

Also on Query3 I never have used a crosstab query before so I do not understand it. When I open it up a msg box appears stating "unknown" in design view I notice in the last field there is what appears to be an error "Expr1: Sum(Nz([EventQuantity],0))". Could you explain what the reason for the expression is? Also, is that an error or am I missing something?

Thank you again for any assistance you have provided.

John

 

[CraigDolphin]

Is there an easy way to have them just fill in the information using a form much like the query3 sub form?

Yes. It's called Excel. There's also a hard way but it's much more complicated to do. Banana posted an prototype example database which demostrates something similar for recording attendance at training classes but, believe you me, you're going to stretch your brain working that out.

The people that have used it can not understand the concept of filling the information on the sub form (frmEvents) of the fmMenu form and then seeing the results on Query3 sub form.

There's not much that can be done about that, except maybe to let them only see one of the forms at a time (one for data entry only, one for seeing results only). User education and/or redesigning the interface is what you need here. Remind them that this is not excel and access will never be excel. Then mention that if they can improve on it using excel you'd be delighted to see their handiwork.

As for that 'error' in the crosstab query. That is not an error. That expression is the means by which the numbers you see in the table are generated. The source query provides a count of one for each record that fits a rule and outcome. This expression sums those counts, or if no matching record is present in the source query then it uses a zero.

 

[CraigDolphin]

Banana's example is in this thread if you choose to look at it http://www.access-programmers.co.uk/forums/showthread.php?t=131902. You will need to do some extensive revamping of it to make it work in your situation.

 

[Casmurbax]

Craig,

thank you very much for your input and help.

I am going to go back and use Excel.

 

[CraigDolphin]

Good luck with your spreadsheet.

 

[Casmurbax]

so I don't look like a total bozo. I did use Excel and make out the spreadsheet. it is attached. not the greatest but it does get done almost everything I wanted.

I appreciate all the help I received in this post and the help provided from the forum.

john