Omiting empty cells in a query to show on Report

[Brian62]

Yes and N/A are as one. Meaning that Yes the document is there but can having a finding because is missing on the doc giving it a write-up. N/A simply means that the doc is not required or is a one-time doc.

"No" means the doc is missing so it is stated as "Could not be located".

Now that I understand what you mean by normalization my only question is that I have a form name "frmRDStudyAudit", can I still continue the way it is setup? I really hate to change this because I want to keep my findings by date since some forms are required to be updated annually. It's a great tracking tool and is where I input my information.

Please look at this and you will see what I am talking about. Thanks!!!

BTW: Sorry for your Brain hurting so much

 

[boblarson]

With a lot of code you could make the existing form work with the proper data structure. However, it would essentially need to be taking the form of an unbound form and code to populate the controls and all. It can be done and it is quite a bit of work. User Interfaces are usually the hardest part to pull off if you want specific ways of looking at it because you have to come up with ways for displaying and adding/editing the data.

So, it really depends on which way you want to go. And I am sorry that you didn't get better direction a long time ago so you weren't so far along with the forms and all.

 

[Brian62]

I consider this a learning experience so sometimes you don't always get what you want out of Access. It does alot but it does have it's limitations.

I wonder if I normalize the DB if I can come up with something similar to what I would like to have. The main thing is to be able to see it as a table so I can see all findings by date.

 

[boblarson]

You can set up a form to select all findings by date (and then you have a subform which shows the values in a vertical sense and not a spreadsheet sense. In other words, you would have it listed like this:

Doc2        Yes
ComDoc2   Comment Here
Doc3         N/A
ComDoc3   Comment Here
etc.

 

[Brian62]

It would need to a form that will look like this by date as the row source:

Date-----01/10/10-----05/10/10-----10/23/10
Doc 2-----Yes------------No-----------N/A
ComDoc2 missing sig---Not dated-----------
Doc3------No------------Yes-----------Yes
ComDoc3--Can't locate----sig missing-------
etc...

This allows me to see what was happening during the inspections by date, doc, and the findings for that doc. Of course it would continue for all docs and Comdoc's. Is this possible without a lot of work? If I can use the date as the main row source that will work for me.

If so, I guess I won't need all the tables that I have already. Not many findings so transfering them over to the new input form won't be a problem. I just started this two months ago.

Once this question is answered I think I can make it all work the way I need it. I'm praying there is a way to do this.....

 

[vbaInet]

Here's a quick and dirty way of performing the concatenation using your original db. Attached.

But continue with Bob's lessons re normalizing your data because you seriously need to sort out your data.

 

[Brian62]

What I have noticed in Test2 Query, the results come out like this:

** Doc #30-5: Could Not Locate ** Doc #37: "Office Use Only" not filled in
Doc # 37: "Office Use Only" not filled in.
Etc...
Memo: Duplicate in sectionDoc #37: "Office Use Only" not filled in

The first line shows ** at the begining and middle showing the separation of the findings.

The one in the middle is normal.

The last one has no separation between the findings (memo & Doc #37)..

In my last message is it possible to do what I had shown?

 

[vbaInet]

Yes, I know. But as mentioned ...

Here's a quick and dirty way of performing the concatenation using your original db. Attached.

 

[Brian62]

Ok. I didn't understand what you menat by "dirty" but I do now. I appreciate both of your help! Thanks!!