I have been futzing around with the attached Db for a while. I have it mostly done, but I still think I can get the data entry to work better.

I need the user to be able to put in ALL medical issues for EVERY Division each time the data entry is done (Weekly)
As it is, I have it set up and working, but if I could get someone to point me in the right direction to maybe make the main form have a tab for each division so that it would be more obvious if something was missing.

I am willing to start over from scratch if I could just get going on this concept. There are only 4 tables in the whole Db and I can re-arrange them as necessary. Please don't do the work for me, just give me nudge in the right direction for the entry form and away I go...

Thanks...

Craig

Your question is not an easy one to answer as we don't know how your data entry people work and what is expected of them. Will they be trained with this db or will it be given to them and then told to go for it?

As someone who has never seen the db before I didn't know what to expect so started clicking buttons. I was able to click the 'Populate the Form' then the Next Record button (without selecting a date) and added lots of blank records so that is something to consider. Also, when is a 'set' of records complete (the records in the subform)? Must all 'Issues' have a quantity or is the default of 0 acceptable?

If this db is used once a week, with absolute certainty, then you might consider removing Division numbers from the combo box as each division is completed. When there are no divisions left showing in the combo box then the user has completed the task.... In a new week set the combo back to show all the records and start again....

Just some thoughts...

Jack

Hi Craig

FirstlyI believe you have a structure problem and it is not easy to see quite what is happening with respect to the t_divLink table.

Suggest you incorporated the DateAdd field from t_DivLink table to t_MedIssueqty, and change the field DivLinkID in t_MedIssueqty to DivID then you can use the following:


The quantity number will be the default value (0)

HTH

Bernie -

There you are! You were here a bit ago and then disappeared. Glad to see you are back and to see that sussed out some cool code for the member.

It is nice to see you lurking here at UA and I hope this email finds you and your family well!

Jack

Hi Jack

I realized as soon as I'd posted the first time that I'd missed the the OPs intermediary table and my suggestion wouldn't work (IMG:http://www.utteraccess.com/forum/style_emoticons/default/blush.gif)
I thought I was quick enough deleting it, and that it wouldn't be missed - should have realized you'd beat me to it!

We are all well, thank you, - celebrated our eldest's 18th Birthday last week, It makes you realize time doesn't stand still!

Hope you and family are well too.

Bernie,

I'm confused...What you 1st suggested wouldn't work? Or were you talking about something else?

Regards,

Craig

FYI - I should have explained what the Db is for. We are keeping track of certain medical issues by week. This Db will allow a person (Trained in it's use) to enter the count of incidents per issue for every week. The way the data is sorted (via cross-tab queries) requires that every division has every item listed every week, even if the value is 0. That is why I went ahead and made the issues auto-populate.

Hi Craig

I posted before Jacks first post, then realized it wouldn't work and so deleted the post, then reposted with a better (I hope) suggestion.

Also, you state you need a record even if the value is 0, However you don't need a record with a zero value to do this, as you can create a query with a right or left join (rather than an inner join) to include ALL divisions; if there is no record in the values table the query can assume a zero value automatically.

HTH

Bernie,

I admit I really don't quite understand the inner vs right or left joins. How would I accomplish this, and would this affect the cross-tab queries that I have set up? Not having to worry about divisions with no data would defiantly simplify things.

Craig

Hi Craig

Do have a look at the attached db, the queries give you some examples of the way the joins can work.
Query1 has no links between the two tables and shows ALL Issues against all divisions
Query2 has Left hand joins between the two tables and selects all the records from query1 and adds to it any valid records from query3
Query3 adds a date criteria to t_MedIssueQty, if you added the critiera later then you would lose the 0 quantity records.
Query2_Crosstab is the crosstab query that also replace the null quantity values with '0' See the 'Sum(NZ([MedIssueQty],0)) expression.

Just to add some fun! the report uses a dynamic query set up in VBA on open event whcih it writes to the reports Record Source. This allows you to set up the report more flexibly and add additional Divisions (albeit a maximum of 13 can be displayed in the sample). It would be possible (say) to set up a multiselect list box and then only display the divisions selected.

The Report queries are based on, (but don't use), the queries above.

Hmmm, looks as if I forgot the attachment (IMG:http://www.utteraccess.com/forum/style_emoticons/default/blush.gif)

Bernie,

O.K. I see were you went with this.

Just like I asked... You pointed me in the right direction and I'll take it from here.

I'll post back if I need any more help, but I think I've got it. (Thanks to you!)

Thanks again!!

Regards,

Craig

Craig, you're very welcome, (IMG:http://www.utteraccess.com/forum/style_emoticons/default/thumbup.gif)

Good luck with your project