Array query - one row to meny rows

  • Thread starter Thread starter erik.dunkars
  • Start date Start date
E

erik.dunkars

Hi

I have a Table (Mdef) with columns (Measueres and BuildingS) containing
Buildning numbers that are comma delimetered AND also includes Arrays
like=>
Measures: BuildingS
01502-Fjv-Am01: 01501-01504, 01512, 01516:
01505-Fjv-Am05: 01507-01510, 01513, 01517:

I need to show one Measure with one building per row like =>
01502-Fjv-Am01: 01501
01502-Fjv-Am01: 01502
01502-Fjv-Am01: 01504
01502-Fjv-Am01: 01512

Please tell me if there are any sulotion ? (I also have one Table with
every building (Building))

Erik
 
I'm sorry to say that this is not a good way of going about things. This is
very simple to do if your table has a single building per measure. As it is
described here, if it is doable at all, the solution will be much more
complex.

In general, SQL (without some assist from a programming language) is not
able to process arrays within a record. That defies the logic of a Relational
Database. There should be only one fact describing the entity that the record
is concerned with in each row of a table. An array is an attempt to capture
multiple facts at one time.

There is also no way in SQL alone to fill in a range (as in 01501-01504).
Doing something like that requires some sort of looping/recursion which is
not an SQL capability.

Good Luck!
 
Hi

I have a Table (Mdef) with columns (Measueres and BuildingS) containing
Buildning numbers that are comma delimetered AND also includes Arrays
like=>
Measures: BuildingS
01502-Fjv-Am01: 01501-01504, 01512, 01516:
01505-Fjv-Am05: 01507-01510, 01513, 01517:

I need to show one Measure with one building per row like =>
01502-Fjv-Am01: 01501
01502-Fjv-Am01: 01502
01502-Fjv-Am01: 01504
01502-Fjv-Am01: 01512

Please tell me if there are any sulotion ? (I also have one Table with
every building (Building))
Click to expand...

This non-atomic, non-normalized BuildingS field will require some
fairly sophisticated VBA code to parse. Is there *ANY* chance that you
could store the data correctly (one value per record) in the first
place?? How are these arrays and lists getting entered?

Just to get your existing data migrated it may be worth trying some
code. Air code, untested, to migrate data from your Mdef table to a
new, two-field table MdefNew:

Public Sub MigrateIt()
Dim rsIn As DAO.Recordset
Dim rsOut As DAO.Recordset
Dim rsB As DAO.Recordset
Dim db As DAO.Database
Dim strBuildings As String
Dim strB() As String
Dim strCrit As String
Dim iLen as Integer
Dim iPos as Integer
' get the current database
Set db = CurrentDatabase
' Open the input and output recordsets
Set rsIn = db.OpenRecordset("[Mdef]", dbOpenDynaset)
Set rsOut = db.OpenRecordset("MdefNew", dbOpenDynaset
' Step through the input recordset
rsIn.MoveFirst
Do Until rsIn.EOF
strBuildings = rsIn!BuildingS ' get the text string
StrB = Split(strBuildings, ",") ' split it into its components
For iPos = 0 to UBound(strB) ' loop through the split array
iLen = InStr(strB(iPos), "-") ' Find the hyphen if any
iLen = 0 Then ' Process single element
rsOut.AddNew
rsOut!Building = strB(iPos) ' add the building
rsOut!Measures = rsIn!Measures
rsOut.Update ' write the new record
Else ' handle arrays
strCrit = "SELECT Building FROM Buildings WHERE Building " _
& ">= '" & Left(strB(iPos), InStr(strB(iPos), "-") - 1) _
& "' AND Building <= '" _
& Mid(strB(iPos), InStr(strB(iPos), "-") + 1) & "'"
Set rsB = db.OpenRecordset(strSQL) ' find the buildings in
' the selected range
rsB.MoveFirst
Do Until rsB.EOF
' loop through all buildings in range, add to rsOut
rsOut.AddNew
rsOut!Building = rsB!Building
rsOut!Measures = rsIn.Measures
rsOut.Update
rsB.MoveNext
Loop ' through rsB
End If
Next iPos
rsIn.MoveNext
Loop ' through rsIn
End Sub


Note: UNTESTED, may need tweaking; and should have error checking
code, e.g. for nonexistant buildings, ill-formed strings, etc.

John W. Vinson[MVP]
 
I have built the Mdef Table in Ms Excel.
It contains about 500 measures. Some of those provides energy to 30-50
buildings but the mean value is about 6-8 buildings i.e if I shall
manually make one measure + building per row it will make about 3000
rows.

I will beg a friend for help with the code
Is there anything we shal keep in our minds except from the advices
under untested ?

Thanks for help

mvh

Erik D
 
I have built the Mdef Table in Ms Excel.
It contains about 500 measures. Some of those provides energy to 30-50
buildings but the mean value is about 6-8 buildings i.e if I shall
manually make one measure + building per row it will make about 3000
rows.
Click to expand...

Not a problem. Access is quite capable of handling 3,000,000 rows;
3000 is a snap. If you're entering this directly into Access, you
could use a form with a subform; on the subform you'ld have a combo
box listing buildings, so you could simply select buildings from the
list, one row after another. With a bit of code you could even select
a range and have it fill in the intermediate values.
I will beg a friend for help with the code
Click to expand...

Feel free to post back here if there are problems!
Is there anything we shal keep in our minds except from the advices
under untested ?
Click to expand...

Keep a couple of layers of backups... <g>

John W. Vinson[MVP]
 
Back
Top