Summary of populated fields in each record. (1 Viewer)

[OBBurton]

IHi,
I have a very simple single-table database with 23 fields. Some of the records have only two or three fields populated. I would like to be able to print a summary of only the populated fields in each record.

It would Ideally look something like:
Record 1 Name
Field 1 Title: Field 1 content - Field 2 Title: Field 2 Content - Field 5 Title: Field 5 content
Field 10 Title: Field 10 Content - Field 11 Title: Field 11 content - Field 12 Title: Field 12 Content
Field 21 Title: Field 21 content - Field 22 Title: Field 22 Content

Record 2 Name
Field 1 Title: Field 1 content - Field 2 Title: Field 2 Content - Field 5 Title: Field 5 content
Field 10 Title: Field 10 Content

Record 3 Name
Field 11 Title: Field 11 content - Field 12 Title: Field 12 Content - Field 21 Title: Field 21 content
Field 22 Title: Field 22 Content

I tried to do this myself but couldn't figure out how. Is there any way to do this in Access? :banghead:

 

[Uncle Gizmo]

The correct answer is you don't do this. First of all you don't have a table with 23 fields and then have only 2 or 3 fields being utilised, this smells of poor design and shouts "loads of room for improvement"...

However if you can't, or won't consider improving your design then you could do it like this.

However you are just digging the hole a lot deeper and making more and more hoops that you will need to jump through to carry on with your poorly designed database.

There is a technique where you pass your table into a record set object then you can scan through this recordset object and make decisions depending on what you find in each individual record. You then pass that information into a new table. I did a comprehensive explanation of this method here:-

Watch "MS Access - Move Info from One Table to Another" on YouTube http://www.youtube.com/playlist?list=PLhf4YcS5AjdrRLrhN2rvTvetBU5wfvfvj

The method shown in the playlist above passes the information into a table using a "fixed" insert SQL statement. You would need to redo this SQL statement and create a dynamic statement that changed depending on how many fields needed to be passed into the new table.

I did another YouTube playlist on building "Easy Search Criteria" which demonstrates how to create a dynamic SQL statement.

Watch "Microsoft Access - Easy Search Criteria" on YouTube:- http://www.youtube.com/playlist?list=PLhf4YcS5Ajdq0vg0GgDZv8pVb9e9miLYI

I think there's enough information and technique explanation in those 2 playlist for you to develop a solution for yourself.

 

[vbaInet]

The correct answer is you don't do this. First of all you don't have a table with 23 fields and then have only 2 or 3 fields being utilised, this smells of poor design and shouts "loads of room for improvement"...

Looks like OBBurton has been concentrating more on aesthetics rather than the table design itself This is the most important part of your build.

 

[OBBurton]

Thanks, I guess. I mean, I do really appreciate your taking the time to answer my post, but I feel you have prejudged my design without knowing the whole story. What I have is a very small personal database having a total of 88 records, eventually topping out at less than 150. It could have easily been done with a spreadsheet. The information associated with each record varies a great deal, some records using only a few fields, some using fifteen, but none of it is repetitive. I am familiar with, if not expert at, normalizing a database. Most of my databases have 5 to 10 tables, but in this case I could think of no real advantage to breaking any of this data into separate tables. But, you are obviously much more experienced than I, so I ask you if there is any advantage to breaking the table into two tables of 12 fields with a one to one relationship? Or is there some other approach that I'm unaware of?

 

[vbaInet]

Thanks, I guess. I mean, I do really appreciate your taking the time to answer my post, but I feel you have prejudged my design without knowing the whole story.

Sometimes I just have a bit of fun with my comments just to see what the poster will come back with, justifying why it was done that way

..., so I ask you if there is any advantage to breaking the table into two tables of 12 fields with a one to one relationship? Or is there some other approach that I'm unaware of?

Hard to see without seeing the full picture but since you're quite offei with normalisation then I don't think there's a need to dwell on this topic.

Can you put some data in a spreadsheet for us to see.

 

[Uncle Gizmo]

>>>> have prejudged my design without knowing the whole story<<<

Yep.... That's all I can do, if there's a better way then I'm all ears.

 

[Uncle Gizmo]

>>>> have prejudged my design without knowing the whole story<<<

Yep.... That's all I can do, if there's a better way then I'm all ears.

Reading my hastily posted reply I realise I haven't gotten my point across very well.

I can only go by what YOU posted. It looks like a design issue, if it walks like a duck, quacks like a duck... so thats what I said. But then you explain your experience in database design; .... afterwards?

Shouldn't you have hinted at that in your original post?

It's like................... well I'm not going there.....

 

[Galaxiom]

... since you're quite offei with normalisation ..

It is a French term: au fait

 

[vbaInet]

It is a French term: au fait

Thanks for the language lesson

 

[OBBurton]

A sincere and heartfelt thank you to Uncle Gizmo and vbaInet!
I really value the time you take to answer my often lame questions. It is part of my learning process and I shouldn't get so defensive when someone points out my errors. I didn't mean to be critical of you, I was just embarrassed. My apologies. After thinking about it, I realized that there really was a certain amount of normalization that could have been done. I also realized that there would be no good reason to go to all the trouble of designing a report that would almost never be printed. So, I'm going to mark this thread solved. Thanks again guys, You've both toughs me a lot!