Denormalizing in VB

[TC]

You clearly understand the normalization aspects of the situation. Your
first three tables are properly normalized, and you're asking how to create
a fourth one to support the users' denormalized view of the world.

I agree with the other poster that you should not store the denormalized
data - ie. stick with the 3 tables you have already. However, you could
generate the denormalized data "on the fly". For example, the following
code, if placed within the Form_Current event of a form that was based on
the facilities table, would populate the txtFacilities textbox with the list
of facility IDs for each Development:

(untested)

dim rs as recordset, s as string
set rs=dbengine(0)(0).openrecordset ( _
"SELECT FacilityID FROM DevelopmentFacility" & _
" WHERE DevelopmentID=""" & me![DevelopmentID] & """")
while not .eof
s = s & ", " & rs![FacilityID]
rs.movenext
wend
me![txtFacilityIDs] = mid$(s, 3)

Of course, if "kitchen" is a facility name or description (not the ID), you
would want to show the names, not the IDs - but you get the idea.

Then you would also have to trap any edits to the "list" field, and
*manually* parse & validate them (using appropriate VBA code) & update the
join table entries accordingly.

IOW, it's do-able, but quite a bit of fiddly work. It would be way easier if
you could convince the users to go to a subform approach (main form =
development, subform = facilities). Or maybe do *both*? Use the "list"
approach for display purposes only, but make them use a subform when they
added, edited or deleted facilities to/in/frm developments?

HTH,
TC

 

[Ron Todd]

Hi Folks.

I don't usually program in VB, but I've offered to do some work for a local
charity that uses an Access2000 DB, and I need to manipulate the data (I
think) programmatically.

I have the main table around which the user's view of the data is based,
"Development", containing details of housing developments. I also have a
table called "Facility" that defines each possible individual Facility that
a Development may provide. Because Development-to-Facility is naturally a
many-to-many relationship, and the users need to see it as a comma-separated
list*, I have introduced a cross-referring table called
"DevelopmentFacility", which (as you might expect) has just 2 columns:
DevelopmentID & FacilityID.

(* By which I mean that the users currently work with reports and screen
views that read:

Development: Glasgow Development
Facilities: Shower room, guest room, kitchen, car parking.

Development: Edinburgh Development
Facilities: Guest room, kitchen, garden.

Development: Aberdeen Development
Facilities: None.)

I would like to do this by creating a new table, with an additional column
"Facilities" to hold the concatenation of facilities from the Facility table
that pertain to each development, but I'm afraid that I'm really all at sea,
as I don't normally work with these technologies. I've just started looking
at using VB for the concatenation, and populating the Facilities column.
Using some sample code from the Help file, I can get an AccessObject
corresponding to each of the table, but as far as I can see, there aren't
many useful properties or methods that I can call upon these in order to
manipulate the tables.

Please help me to crack this (as it's for a good cause!). In the first
instance, I'd be really grateful to know of the approach I'm taking
(creating a new table in the DB, and populating its Facilities column from
VB by denormalizing the Facility table) is viable - and also if there's some
obvious better way.

Additionally, I could really do with some outline of the steps I need to
take to get this working (ideally with stages that I can evaluate against as
I develop) - or ideally, some sample code.

Hope that's all clear enough, and someone's able to help.

Thanks in advance!

Ron

 

[TC]

The code should work fine for you.

Before you do the report, create a new Module, put a public sub in that
module, & put my code in that sub:

Public Sub z()
[my code]
end sub

Also change the code to display mid$(s,3) with a msgbox statement, instead
of trying to put it into the (non-existant) textbox. Then go to the debug
window (Ctrl-g), type z and press return. This will call that sub, & run the
code. This gives you an easy environment to fix any errors. Then get it
going in a report.

Good luck!
TC

Thanks TC.

For data entry, I do provide a subform - and that's fine. When the data is
retrieved in the form of a report, however, I need to concatenate as
described. I'll give it a go as you describe.

You clearly understand the normalization aspects of the situation. Your
first three tables are properly normalized, and you're asking how to create
a fourth one to support the users' denormalized view of the world.

I agree with the other poster that you should not store the denormalized
data - ie. stick with the 3 tables you have already. However, you could
generate the denormalized data "on the fly". For example, the following
code, if placed within the Form_Current event of a form that was based on
the facilities table, would populate the txtFacilities textbox with the list
of facility IDs for each Development:

(untested)

dim rs as recordset, s as string
set rs=dbengine(0)(0).openrecordset ( _
"SELECT FacilityID FROM DevelopmentFacility" & _
" WHERE DevelopmentID=""" & me![DevelopmentID] & """")
while not .eof
s = s & ", " & rs![FacilityID]
rs.movenext
wend
me![txtFacilityIDs] = mid$(s, 3)

Of course, if "kitchen" is a facility name or description (not the ID), you
would want to show the names, not the IDs - but you get the idea.

Then you would also have to trap any edits to the "list" field, and
*manually* parse & validate them (using appropriate VBA code) & update the
join table entries accordingly.

IOW, it's do-able, but quite a bit of fiddly work. It would be way

easier

if
you could convince the users to go to a subform approach (main form =
development, subform = facilities). Or maybe do *both*? Use the "list"
approach for display purposes only, but make them use a subform when they
added, edited or deleted facilities to/in/frm developments?

HTH,
TC

naturally

a at
sea, there's
some against

 

[Albert D. Kallal]

You don't possibility want to store that data separated by commas. Virtually
every book, article or person will tell you to avoid this.

I mean, what happens when a person needs to add, or remove a facility for a
given development. All of a sudden, you need REAMS AND REAMS of code to keep
this new comma field in sync. Simply put, don't even consider going there.
You can write a mountain of code to scan this field when new faculties are
added (cause it might already be there), and a whole bunch more code to scan
this thing when you edit, change, or even delete a facility.

The solution is to build a nice form that lets you enter a new Development.
For the list of "facilities" that each development has, you simply build a
sub form based on your table called DevelopmentFacility. (use a combo box in
the sub form in continues mode). Your users will be able to easily see what
faculties each development has. Not only that, but it will TAKE ZERO CODE to
edit, manage and even add/delete facilities for a given development. (a sub
form in ms-access will mange this whole relationship for you with just the
UI....no code needed!). Build the sub form on the child table. In that form,
use the combo box wizard to lookup the id to the facilities table.

You can certainly grab the many facilities and create a comma separated list
at report time, but to even consider in any way shape or form to store that
data will be the worst decision you can make!

So, get your self a book on ms-access. You can also read my thoughts on
using a sub-form (which is what you need!).

http://www.attcanada.net/~kallal.msn/Articles/fog0000000005.html

I would like to do this by creating a new table, with an additional column
"Facilities" to hold the concatenation of facilities from the Facility

table

Nope! As mentoned, you don't have to do that. Use a sub-form with a combo
box.

For some reading on data normalizing:

ACC2002: Database Normalization Basics
http://support.microsoft.com/?id=283878

Database Normalization Tips by Luke Chung
http://www.fmsinc.com/tpapers/genaccess/databasenorm.html

Data Normalization Fundamentals by Luke Chung
http://www.fmsinc.com/tpapers/datanorm/index.html

324613 - Support WebCast: Database Normalization Basics
http://support.microsoft.com/?id=324613

 

[Ron Todd]

Just to clarify:

No, I don't intend storing the denormalized data string in the database
permanently. I wouldn't want the new table that I described to be part of
the DB's ERD. I was thinking of it as the result of a query or as a
temporary table - in either case, the data wouldn't need to be persisted,
kept in sync, or maintained in any way.

I want to be able to produce (printable) reports that use the
comma-separated format, and I was considering an intermediate DB object as a
means of doing this. Since I'm familiar with the concept of Access reports
being based upon queries or tables, I thought that it would be easiest to
place the formatted data into a table - which I would either create
dynamically or completely clear out before populating (this may, of course,
be the wrong way to go about creating the report altogether).

Thanks for all responses to date. I will read and digest them, and may come
back with requests for clarification if that's OK.

Any further responses that might help are also very welcome.

Ron

 

[Ron Todd]

Thanks TC.

For data entry, I do provide a subform - and that's fine. When the data is
retrieved in the form of a report, however, I need to concatenate as
described. I'll give it a go as you describe.

You clearly understand the normalization aspects of the situation. Your
first three tables are properly normalized, and you're asking how to create
a fourth one to support the users' denormalized view of the world.

I agree with the other poster that you should not store the denormalized
data - ie. stick with the 3 tables you have already. However, you could
generate the denormalized data "on the fly". For example, the following
code, if placed within the Form_Current event of a form that was based on
the facilities table, would populate the txtFacilities textbox with the list
of facility IDs for each Development:

(untested)

dim rs as recordset, s as string
set rs=dbengine(0)(0).openrecordset ( _
"SELECT FacilityID FROM DevelopmentFacility" & _
" WHERE DevelopmentID=""" & me![DevelopmentID] & """")
while not .eof
s = s & ", " & rs![FacilityID]
rs.movenext
wend
me![txtFacilityIDs] = mid$(s, 3)

Of course, if "kitchen" is a facility name or description (not the ID), you
would want to show the names, not the IDs - but you get the idea.

Then you would also have to trap any edits to the "list" field, and
*manually* parse & validate them (using appropriate VBA code) & update the
join table entries accordingly.

IOW, it's do-able, but quite a bit of fiddly work. It would be way easier if
you could convince the users to go to a subform approach (main form =
development, subform = facilities). Or maybe do *both*? Use the "list"
approach for display purposes only, but make them use a subform when they
added, edited or deleted facilities to/in/frm developments?

HTH,
TC

Hi Folks.

I don't usually program in VB, but I've offered to do some work for a local
charity that uses an Access2000 DB, and I need to manipulate the data (I
think) programmatically.

I have the main table around which the user's view of the data is based,
"Development", containing details of housing developments. I also have a
table called "Facility" that defines each possible individual Facility that
a Development may provide. Because Development-to-Facility is naturally a
many-to-many relationship, and the users need to see it as a comma-separated
list*, I have introduced a cross-referring table called
"DevelopmentFacility", which (as you might expect) has just 2 columns:
DevelopmentID & FacilityID.

(* By which I mean that the users currently work with reports and screen
views that read:

Development: Glasgow Development
Facilities: Shower room, guest room, kitchen, car parking.

Development: Edinburgh Development
Facilities: Guest room, kitchen, garden.

Development: Aberdeen Development
Facilities: None.)

I would like to do this by creating a new table, with an additional column
"Facilities" to hold the concatenation of facilities from the Facility table
that pertain to each development, but I'm afraid that I'm really all at sea,
as I don't normally work with these technologies. I've just started looking
at using VB for the concatenation, and populating the Facilities column.
Using some sample code from the Help file, I can get an AccessObject
corresponding to each of the table, but as far as I can see, there aren't
many useful properties or methods that I can call upon these in order to
manipulate the tables.

Please help me to crack this (as it's for a good cause!). In the first
instance, I'd be really grateful to know of the approach I'm taking
(creating a new table in the DB, and populating its Facilities column from
VB by denormalizing the Facility table) is viable - and also if there's some
obvious better way.

Additionally, I could really do with some outline of the steps I need to
take to get this working (ideally with stages that I can evaluate

against

as
I develop) - or ideally, some sample code.

Hope that's all clear enough, and someone's able to help.

Thanks in advance!

Ron

 

[Ron Todd]

Thanks, Albert.

I am Ok on normalization, and have put the DB into BCNF. I also have data
*entry* using sub-forms, exactly as you describe. (I'm very inexperienced
with Access - but not *that* inexperienced.) My current problem is that I
want to produce a report that shows the normalized (3NF++) data in what
amounts to a denormalized view - see OP for 3 instances of the sort of
output that is required.

As a basis for the required report, I would usually (with my limited Access
experience) prepare the data using a query, then design the report to use
fields from the query. Since the data is only going to persist for the
duration of the report, there is no question of denormalizing the
fundamental structure of the DB.

 

[Albert D. Kallal]

Good stuff here Ron.

Ok, here is the solution:

Fire up the query builder and drop in our DevelopmentFacility table. Drop in
the field that links to the Development ID. Lets also drop in the FactilyID.
Now we do need the Description, so, now drop in the Facility table,a nd drop
a join line from our DevelopmentFacility table (lookup id to Facility) to
the Facility table. Now, drop in the description. Take a quick pop into the
data sheet view, and you should have a nice view like:

DevelpmentId, FacitlityID, FacilityDesctionText.

Save our query. lets call it qryFactDesc

So, in your reprot, you add a function like:


Public function MyFactList(dvId as Varient) as Varient

dim rstRecs as dao.RecordSet
dim strMySql as string
dim strResult as string

if isNull(dvId) = false then
strMySql = "select FacilityDescriptonText from qryFactDesc " & _
"where FaciticlityID = " & dvId

rstRecs = currentdb.OpenRecrdSet(strMySql)
do while rstRecs.Eof = false
if strResult <> "" then
strResult = strResult & ","
endif
strResult = strResult & rstecs!FacilityDescriptionText
rstRecs.MoveNext
loop
MyFactList = strResult
endif

end function


Now, anywhere in your report, you can simply place a text box, and use the
function as the source, and pass the development id

=MyFactList([devid])

there is also general solution at:

http://www.mvps.org/access/modules/mdl0004.htm

 

[Albert D. Kallal]

Note the previous was air code....

You *really* should close the recordset also:

MyFactList = strResult

rstRecs.Close
set rstRecs = nothing

endif

 

[Ron Todd]

Thanks *very* much TC & Albert.

I'm quite sure that it will be no problem, now!