Hello, please forgive me this may be difficult to explain and understand. I have built a project management database. The user chooses "pay items" from a specific projects daily report. The pay items are built during setting up the project in the data base and are assigned specific job numbers. When a pay item from another job number is incorrectly selected for a daily report it causes all kinds of problems in other reports of the data base. How can I have the daily report form only accept pay items based on the job number selected on the daily report. In other words' a pay item from job number 9401 is selected for job number 9584 even though they may seemly be the same pay item they are not.

Hi, welcome to UA.



I'd like to give a bit of background on how the data in an Relational Database Management System works (RDMS). It is not possible that a Report alone causes problems with other reports... here's why:



You have one style object that contains raw data. These are Tables.

You have one style object that "views" this data. These are Queries. They pull data as directed from the Tables, sorting, organizing and restricting records as required.

You have one style object that provides an "interaction layer" to the raw data. These are Forms... they allow the data to be viewed and edited.

And finally, you have one style object that provides a "user view" of the data... these are Reports. Reports merely show data, but do not change anything* and therefore a report is shown, it cannot affect other reports, because it would actually need to change the data rather than just view it

*Reports can have code that runs when the report runs, and data can be changed via code, but this is not a standard practice (generally speaking) and as such reports themselves are not able to alter data)

In a nutshell, every time you run a report, you're just grabbing some data from a table; a raw container or data, but not changing anything.

That said, can you explain how you think that the incorrect selection for a report is causing problems with other reports?

Cheers,

Jleach, Thank you for your reply. I must first point out that I have no formal training in Access. This probably wont be the first time I am using terms incorrectly. With that being said, Thank you for your breakdown of how data is shown. I understand that it is not the report that is causing the problem it is merely displaying what it is told to display. The problem could be in the underlying relations. Although if this is the case, it is a problem that I will have to work around. The reports I was referring to show cumulative totals of pay items installed. The criteria for the query that the report runs is based on the job number. I believe this is why my report is showing incorrect data. What I would like to implement is a procedure that will notify the user that they have selected a pay item that does not match the job number on the daily report.

I understand that it is difficult to show you the whole picture. let me know if I can supply you with more information, screen shots or etc.

We need to understand your table structure to help.

But based on what you have said. You need to have a table of pay items and their associated jobs. You then need to filter the list that you select pay items from for the current job. This will prevent the user from selecting an incorrect pay item.

Without seeing your structure its impossible to be more specific.

(IMG:style_emoticons/default/welcome2UA.gif)

Drwyer - screenshots are always good and there is nothing like a sound data model design. Detailed descriptions of the expected behavior, table structures and form usage are also good to post when asking for help. It sounds like you're just getting started with Access, so I'd like to point out the following links for reading/learning pleasure . . . .

• Crystal's ACCESS Basics for Programming
• Database planning tutorial Part I
• Database planning tutorial Part II
• argeedblu's FAQ on Design Strategies
• Noah's FAQ on Normalization
• Jerry's FAQ on Normalization
• GroverParkGeorge's PDF on Normalization
• Jerry’s FAQ on Fully Qualified Naming
• Jerry's FAQ on Glossary
• Danny's FAQ on Reserved Words
• Autonumbers - What They Are / Are Not
• Primary Keys - Natural vs Surrogate
• Naming Conventions - Hungarian Notation
• Database Answers (free models)
• MSKB 283878: Description of the database normalization basics
• The Evils of Lookup Fields in Tables
• Sharing/Splitting a Database
• Access Tutorial
• Access VBA Tutorial

Meanwhile, we're to help get you going in the right direction.

Cyber Cow,

Thank you for the greetings and light reading materials. I have always shied away from forums due to the fact that I am self taught in access. As far as a sound model, I am not sure if I can produce such a beast. My data base is built around the need to have all aspects of tracking a construction project at the project managers fingertips. The data base is driven around a universal daily report. From this daily report I can track labor, cost, revenue, inventory and other miscellaneous and detailed items. I will look into the links you provided.

Just for the record, a great many people using Access are self taught. Or rather, maybe I should say it was the great people in forums that have been the teachers. This was certainly the case for myself, and many other excellent Access developers that I know: all self taught.

That's not to say that you can't be a good Access developer if you have taken classes for it, but a good many people never really planned on learning Access, but instead found themselves in situations where it'd help, and went ahead and tried to figure it out, generally with the help of forums and lots of reading.

This is exactly how I did it, and many others I know as well (maybe half the developers I know?)

So, don't be shy! That's what we're here for.

Cheers,

Drwyer - I too am self-taught in Access, but after discovering this sight back in 2002, I've become a staunch advocate of working with otherrs as opposed to working alone. So many of the members here noteworthy contributors and have insights that they share. When I worked on Access by myself (before I came to UtterAccess) I thought knew alot. I have since revised that thinking and now know that there is MUCH I do not know about Access. I learn something new here everyday because of the vast perspective that the UtterAccess community provides.

Regarding a 'sound' data model - it is crucial to have a solid table structure to facilitate growth of an Access application. Think of the table structure (relationships) as the foundation of a house or other building. Once you lay the foundation, you're pretty much constrained to that outline. and if you want to change the foundation after you've built the upper structure (queries, forms & reports) on top of that foundation, you quickly see how much more work is involved in changing those objects.

That's part of the big reason UtterAccess exists - to help those interested in learning to work with Access and do it right.

The more you dig in, the more questions will likely be raised. We're here for ya.

CyberCow,

I am quickly realizing that my original posting of user error handling can be accomplished by more simple methods. I was reading some of the article links you provided and am questioning my knowledge of basic design, normalization and so on. I have done a lot of research on normalization. ultimate I end up proceeding with the table design and work backwards. Is it customary for members to ask for help from the ground up with a concept? I would like to create a companion data base to my original one. The new one is for estimating. I fully understand mastering the structure is essential and I tend to do it backwards. Am I in the wrong arena for this type of support?

Hi,

Though the question is directed to Mark, we'd all answer the same, so I'll go ahead (IMG:style_emoticons/default/thumbup.gif)

You definately don't want to be doing it backwards. If you do, the best it's going to be is a "sketch" which you would then re-write completely under a new model after you've been able to "see the light."

Most people find that it's easiest to start with a pen and paper, and actually lay out the entire structure there, where it's easy to make adjustments (or maybe visio if you're fluent with that), but in either case, you always want to start with a planned out design (a correct one) before you even open Access and start creating tables.

One helpful thing that can work to your advantage is to do some sketches of the forms that you'd like to do, and maybe the reports as well. Again, on paper is generally easiest... do one sketch for your table designs and one for your forms, and you should be able to see how they will match up, provided you obtain a decent understanding of the core normalization concepts. One things to keep in mind here is Queries and what their purpose is. Queries pull data from your tables and "present" it in an organized way which your forms/reports will show/work with. This is helpful in trying to visualize how everything will work together.

I wouldn't say that it's absolutely required to have every detail worked out before you start creating tables, but you will want to have the basic overall design down and making sense.

Example databases can be a lot of help in trying to understand how it all works together as well. One problem with starting off in Access is that we would all have greatly benefitted from knowing everything upfront in order to see how it all fits together, but unfortunately it just doesn't work that way. Example databases are a valuable tool in providing some insights to that vision. Access used to ship with a sample database called Northwind... I'm not sure if it still does, if not you can easily download it (search "Northwind Database" in google for a link). This is the standard sample, and many MS tutorials are given using this as the database to work with.

Regarding forum help in these matters, UA is a great place to get that help. All of our experienced members here are very happy to see people asking questions from the ground up, concentrating on normalization first, because that's the foundation of everything else (at times we may get frustrated with members who request help but are unwilling to "go back" for this type of much needed normalization help).

UA has a Tables & Relationships board that is perfect for these types of questions.

Best of luck!

Thanks for the advise. I am going to sketch some tables and relations with narratives and see what the members think. The bad thing about my line of work is there are very few constants and a whole lot of variables that I find difficult to translate to related tables. I fully agree with the standard concept. It is just that sometimes when I cant figure out the right way to proceed I will go ahead knowing full well it is not correct and then fix it. I guess I need to see it break before I can fix it. Anyways, I will develop a clear but brief presentation and see what the members think. Thanks again.

In regards to the "Tables & Relationships Board". How do I direct my questions or topics to them?

When you view the board and see the list of posts, at the top right of the entire list you'll see a "New Topic" button. Click it, choose your Access Version from the dropdown next to the Title, and write up your post.

Cheers,