Can a report and form be bound to same query and view different results? (1 Viewer)

Well this might possibly get a few interesting responses, but okay.

This DB is made to report up several things to higher management about contractor performance, plus it also keeps the contractors informed of what was observed that takes place each night. First there is there is the coversheet, which detemines which documents are to be sent to which group of people. Some go to management, some go to contractors, and some go to both. Yes I could have opted for the simple drop down menu, but I just wanted to do this instead, it took up more room on the report because there was a huge amount of blank space.

Next we have a form called an RSHA. That tells the store how many cases of product were received that day on deliver trucks, whether it was for store use, to be stocked by contractors, stocked by vendors, etc... This form is pretty simple and straight forward.

Next comes the recap form. what this does is breaks every case down by aisles, and then also if there were cases that were not stocked by the contractors and for what reason. Then it calculates what was stocked during the daytime by contractors versus vendors. Then it totals out all of these numbers to generate a report.

Then you have a case count sheet, which keeps track of all the recap entries for an entire month.

Then after that is the monthly RSHA, which keeps track of all the daily RSHA entries for the month.

Lastly there is a form for tracking the Randoms Sample Surveillance. This is the simplest of all, it is simply 2 input boxes (which represent different categories for deficiencies), the user inputs how many deficiencies were found in each category. Then I have to have all this generate reports for my DB each day. Then at the end of the month also.

I do have the original DB I made several years ago, we use it daily. It is badly made, it was a first attempt at this. I am trying to clean up a lot of the mistakes I made the first time around.
 
Well this might possibly get a few interesting responses, but okay.

This DB is made to report up several things to higher management about contractor performance, plus it also keeps the contractors informed of what was observed that takes place each night. First there is there is the coversheet, which detemines which documents are to be sent to which group of people. Some go to management, some go to contractors, and some go to both. Yes I could have opted for the simple drop down menu, but I just wanted to do this instead, it took up more room on the report because there was a huge amount of blank space.

Next we have a form called an RSHA. That tells the store how many cases of product were received that day on deliver trucks, whether it was for store use, to be stocked by contractors, stocked by vendors, etc... This form is pretty simple and straight forward.

Next comes the recap form. what this does is breaks every case down by aisles, and then also if there were cases that were not stocked by the contractors and for what reason. Then it calculates what was stocked during the daytime by contractors versus vendors. Then it totals out all of these numbers to generate a report.

Then you have a case count sheet, which keeps track of all the recap entries for an entire month.

Then after that is the monthly RSHA, which keeps track of all the daily RSHA entries for the month.

Lastly there is a form for tracking the Randoms Sample Surveillance. This is the simplest of all, it is simply 2 input boxes (which represent different categories for deficiencies), the user inputs how many deficiencies were found in each category. Then I have to have all this generate reports for my DB each day. Then at the end of the month also.

I do have the original DB I made several years ago, we use it daily. It is badly made, it was a first attempt at this. I am trying to clean up a lot of the mistakes I made the first time around.
OK thanks for the explanation.
To address the multiple checkbox's I would suggest that your relationships and Data Input Form would look something like that shown in the attached.

Not knowing enough about your process I have just made a guess at some field names.
 

Attachments

  • FormLayout.png
    FormLayout.png
    29.7 KB · Views: 8
  • RI.png
    RI.png
    26.8 KB · Views: 8
I appreciate the effort you put into that, I will gear my setup more towards that as best as I can, thank you.
 
Nice attempt to normalize db from Mike. However, not quite understanding the relationships yet. Also, report does not work now and will likely involve a CROSSTAB query to accomplish same arrangement (and this presents new headaches to deal with), otherwise a complete redesign. So the question is, must you have this report look exactly like this?

It is a balancing act between normalization and easy of data entry/output. Google "Normalize until it hurts denormalize until it works." Here is one Normalize until it hurts denormalize until it works

If primary purpose of this table is production of this exact report and there is little to no statistical analysis nor search/filter based on data other than the date nor relates to any other table, normalizing this table may not be desirable.

More questions:
Must coversheet report always be for the most recent date - user is not permitted to select another date to base output on?
Why would distribution of any report change day to day?
 
Last edited:
I appreciate the effort you put into that, I will gear my setup more towards that as best as I can, thank you.
Hi

June asked has the Report got to look like your original?

See the amended Report in the attached. I think this layout reflects your table structure.
 

Attachments

Nice attempt to normalize db from Mike. However, not quite understanding the relationships yet. Also, report does not work now and will likely involve a CROSSTAB query to accomplish same arrangement (and this presents new headaches to deal with), otherwise a complete redesign. So the question is, must you have this report look exactly like this?

It is a balancing act between normalization and easy of data entry/output. Google "Normalize until it hurts denormalize until it works." Here is one Normalize until it hurts denormalize until it works

If primary purpose of this table is production of this exact report and there is little to no statistical analysis nor search/filter based on data other than the date nor relates to any other table, normalizing this table may not be desirable.

More questions:
Must coversheet report always be for the most recent date - user is not permitted to select another date to base output on?
Why would distribution of any report change day to day?
No, basically the report can look like anything I want it to just so long as I get the information on it across. I have already redone it, removed all the check boxes and just went with combo boxes that state, “Management”, “Contractor”, “Both”, or “Neither”.

Next thing is I can’t seem to get the link you posted to work that is associated with the “Normalize until it hurts…”.

As I said in a post a while back, most of this data does not relate to each other in anyway other than the dates. I mean I could probably find a way through it with the Daily RSHA and the Monthly RSHA, and the Daily Recap with the Monthly Case Count. But I am taking things one problem at a time before moving forward to the next.

Lastly, yes all the daily documents must be for the recent date. Users can scroll through, but unless they need to pull up something from previous days the only relevant data is the most recent entries. The distribution could change because on some days they might request we submit documents we do not normally do, such as the daytime stocking sheets, or we might be over on the overwrites, and have to submit a memorandum to explain why. Those are out of the ordinary situations but I want to make sure I am covered either way.

If you had to simply look at the tables with the data input on my database, you would have no clue what any of it meant. Without the use of the forms and reports to put any of it into context it is utterly meaningless.

Keep this in mind, I work overnight, so for example, lets say I went in to work on Nov 22nd. We are writing everything up based on that day, but while at work it goes past midnight into the 23rd. So just because we are physically on the 23rd, we are still documenting for the 22nd.

I hope this clears up some of the questions that have come to mind.
 
No, basically the report can look like anything I want it to just so long as I get the information on it across. I have already redone it, removed all the check boxes and just went with combo boxes that state, “Management”, “Contractor”, “Both”, or “Neither”.

Next thing is I can’t seem to get the link you posted to work that is associated with the “Normalize until it hurts…”.

As I said in a post a while back, most of this data does not relate to each other in anyway other than the dates. I mean I could probably find a way through it with the Daily RSHA and the Monthly RSHA, and the Daily Recap with the Monthly Case Count. But I am taking things one problem at a time before moving forward to the next.

Lastly, yes all the daily documents must be for the recent date. Users can scroll through, but unless they need to pull up something from previous days the only relevant data is the most recent entries. The distribution could change because on some days they might request we submit documents we do not normally do, such as the daytime stocking sheets, or we might be over on the overwrites, and have to submit a memorandum to explain why. Those are out of the ordinary situations but I want to make sure I am covered either way.

If you had to simply look at the tables with the data input on my database, you would have no clue what any of it meant. Without the use of the forms and reports to put any of it into context it is utterly meaningless.

Keep this in mind, I work overnight, so for example, lets say I went in to work on Nov 22nd. We are writing everything up based on that day, but while at work it goes past midnight into the 23rd. So just because we are physically on the 23rd, we are still documenting for the 22nd.

I hope this clears up some of the questions that have come to mind.
Hi
Because we do not have a full picture of what your business process is, we are making suggestions on what we see in your current table example.

To understand your business process we need a step by step explanation of what you do from when you sign on to your shift.

You mention "the Daily RSHA and the Monthly RSHA, and the Daily Recap with the Monthly Case Count", can you explain what each of these areas are?
 
Hi, I explained what they were for in post 23. If you need more detail than that I will see what I can do to elaborate it more. But I am thinking this post has gotten away from the original question I was asking. I am just trying to find code that will allow me to view only the most recent date on a report without the need to view that record on the form before the report opens AND without the need to use an additional SELECT TOP 1 query. It may not be possible. But I am hoping it is. Anyway, I do appreciate the interest in helping me, and I have seen posts before get way off topic the more questions that are asked.
 
Well this might possibly get a few interesting responses, but okay.

This DB is made to report up several things to higher management about contractor performance, plus it also keeps the contractors informed of what was observed that takes place each night. First there is there is the coversheet, which detemines which documents are to be sent to which group of people. Some go to management, some go to contractors, and some go to both. Yes I could have opted for the simple drop down menu, but I just wanted to do this instead, it took up more room on the report because there was a huge amount of blank space.

Next we have a form called an RSHA. That tells the store how many cases of product were received that day on deliver trucks, whether it was for store use, to be stocked by contractors, stocked by vendors, etc... This form is pretty simple and straight forward.

Next comes the recap form. what this does is breaks every case down by aisles, and then also if there were cases that were not stocked by the contractors and for what reason. Then it calculates what was stocked during the daytime by contractors versus vendors. Then it totals out all of these numbers to generate a report.

Then you have a case count sheet, which keeps track of all the recap entries for an entire month.

Then after that is the monthly RSHA, which keeps track of all the daily RSHA entries for the month.

Lastly there is a form for tracking the Randoms Sample Surveillance. This is the simplest of all, it is simply 2 input boxes (which represent different categories for deficiencies), the user inputs how many deficiencies were found in each category. Then I have to have all this generate reports for my DB each day. Then at the end of the month also.

I do have the original DB I made several years ago, we use it daily. It is badly made, it was a first attempt at this. I am trying to clean up a lot of the mistakes I made the first time around.
Hi
I note in Post #23 that "you have the original DB I made several years ago".
Are you able to upload a copy of this db with a few random records?
As for the Report, I would think the easiest option would be to have 2 queries and 2 Reports.
 
Yes currently that is what I am using. And I would prefer not to upload that original DB as it would be to embarassing, I made it so very badly.
 
You should not be embarrassed because at least you have made an attempt at creating the database as well as you knew how.

We are only interested in seeing the whole picture and hopefully help you if necessary, to change for the better.
 

Users who are viewing this thread

Back
Top Bottom