Spreadsheet like 1

[jammerdk]

Hi Guys

trying to create a query for a form making a spreadsheet.

I got 1 row of values in my table now i'd like for the query
creating a new row for every 5 of data.

like this

My table looks like this at the moment....

Dim
1
2
3
4
5
6
6
7
8
9


Would like to get my query like this

Dim 1 Dim 2
1 6
2 6
3 7
4 8
5 9

I know you guys could help me out on this )

 

[SkipVought]

Please explain the LOGIC in dividing the data?

Is it related to the POSITION of the data in the first series or the VALUE of the data in the first series?

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[jammerdk]

to get a spreadsheet look on my form eventhough my table only got 1 coloumn of data

 

[SkipVought]

My question is, what is the LOGIC that you want to use to get the data into more than one column?

Will there ever be MORE data than the 10 row that you show in your example?

Will there ever be LESS data than the 10 row that you show in your example?

Is there a ROW limit in your Form?

Please answer ALL questions, CLEARLY, CONCISELY & COMPLETELY.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[MajP]

Not sure what spreadsheets you have been looking at, but I have never seen a spreadsheet with the same column of data repeated in multiple times. Every spreadsheet I have ever seen has column headers, but each column represents different group of data. What are you really trying to do? To me a standard datasheet looks like a spreadsheet. Each column represents a group of related data. You can make a multi column form.

 

[jammerdk]

Hi SkipVougt

YeaH i'd like to get given a maximum of rows (for example 12) on each column on my form. Yes there would get more and more row as times passes.

trying to make a living scheme on my form.

Dim 1 Dim2 Dim3 Dim4
1 10 17 26
2 10 18 27
3 11 19 28
4 12 20 29
5 12 22 26
6 14 23 27
7 15 24 29
8 15 25 30
9 16 25 32
10 16 25 32
10 17 25 35
10 17 26 40

 

[SkipVought]

So then what's the maximum number of columns you will want to display on a form?

What happens if there is more data than will fit on the form?

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[jammerdk]

it's 20 columns, if there's more data than can fit the form it creates a new tab on the forms tab )

 

[MajP]

This solution works, but it is not very eloquent.

1) tell the procedure how many rows you want.
2)calculate the total records
3) divide it by the number of desired rows to determine columns
4)create a new table with the desired columns (delete old table if it exists).
5)fill the table down and then across
6) open the form and set the recordsource to the new table

You could build the form dynamically, but that is another post.

This code works with any table: Provide a table name and field name.

Example. Northwind database with Table "Orders" and field "OrderID". The data looks like

[tt]
Order ID
10249
10250
10251
10252
10253
10254
10255
10256
10257
10258
10259
10260
10261
10262
10263
10264
10265
10266
10267
10268
10269
10270
10271
10272
10273
10274
10275
10276
10277
10278
10279
10280
10281
....
11077
[/tt]
There is 847 records. It produces the tblSpreadSheet with 42 columns. Something like
[tt]
OrderID1 OrderID2 OrderID3 OrderID4 ............OrderID42
10249 10269 10289 10309 11076
10250 10270 10290 10310 11077
10251 10271 10291 10311
10252 10272 10292 10312
10253 10273 10293 10313
10254 10274 10294 10314
10255 10275 10295 10315
10256 10276 10296 10316
10257 10277 10297 10317
10258 10278 10298 10318
10259 10279 10299 10319
10260 10280 10300 10320
10261 10281 10301 10321
10262 10282 10302 10322
10263 10283 10303 10323
10264 10284 10304 10324
10265 10285 10305 10325
10266 10286 10306 10326
10267 10287 10307 10327
10268 10288 10308 10328
[/tt]

call the procedure

Public Sub test()
  createSpreadSheet "Orders", "OrderID", 20
End Sub

code

Public Sub createSpreadSheet(tblName As String, fldName As String, intRecordsDisplayed As Integer)
  Dim tblSpreadSheet As DAO.TableDef
  Dim rsSpreadsheet As DAO.Recordset
  Dim fld As DAO.Field
  Dim rs As DAO.Recordset
  Dim lngRecords As Long
  Dim intFields As Integer
  Dim fldType As Long
  Dim intCount As Integer
  Set rs = CurrentDb.OpenRecordset(tblName, dbOpenDynaset)
  fldType = rs.Fields(fldName).Type
  If Not (rs.BOF And rs.EOF) Then
     rs.MoveFirst
     rs.MoveLast
  End If
  lngRecords = rs.RecordCount
  intFields = Fix(lngRecords / intRecordsDisplayed) + 1
  If tblExists("tblSpreadSheet") Then
    CurrentDb.TableDefs.Delete ("tblSpreadSheet")
  End If

  Set tblSpreadSheet = CurrentDb.CreateTableDef("tblSpreadSheet")
    For intCount = 1 To intFields
       'Debug.Print intFields
       Set fld = tblSpreadSheet.CreateField(fldName & intCount)
       tblSpreadSheet.Fields.Append fld
       Set fld = tblSpreadSheet.Fields(fldName & intCount)
       fld.Type = fldType
    Next intCount
  CurrentDb.TableDefs.Append tblSpreadSheet
  Set rsSpreadsheet = CurrentDb.OpenRecordset("tblSpreadSheet", dbOpenDynaset)
  
  For intCount = 1 To intRecordsDisplayed
    rsSpreadsheet.AddNew
    rsSpreadsheet.Update
  Next intCount
  
  intCount = 0

  rs.MoveFirst
  rsSpreadsheet.MoveFirst
  Do While Not rs.EOF
    If rs.AbsolutePosition Mod (intRecordsDisplayed) = 0 Then
      rsSpreadsheet.MoveFirst
      intCount = intCount + 1
      Debug.Print rs.AbsolutePosition
    End If
    rsSpreadsheet.Edit
    rsSpreadsheet.Fields(fldName & intCount) = rs.Fields(fldName)
    rsSpreadsheet.Update
    rsSpreadsheet.MoveNext
    rs.MoveNext
  Loop
    
End Sub

Public Function tblExists(strTblName As String) As Boolean
  Dim tdf As TableDef
  For Each tdf In CurrentDb.TableDefs
    If tdf.Name = strTblName Then
      tblExists = True
    End If
  Next tdf
End Function

This seems overly complicated, there is probably a better way to do this. Actually, now that I posted that I think you could do this with a crosstab query. I will give it a try.

 

[jammerdk]

Hi MajP

Yeah it seems complicated...(my first thought were also a crosstab query)

have tried your suggestion and it do exactly what I want transfer a single row into a spreadsheet.... only thing it's the ID field and not the actually number field as it should be. (suggestions?)

 

[MajP]

it's the ID field and not the actually number field as it should be

I do not understand the question. It should work with any field or table.

 

[jammerdk]

Yeah it works fine on the ID field but when I use my numberic field(Dim) it says fieldtype not valid.

 

[jammerdk]

my numberic field got 2 decimal ...maybe this be it?

 

[jammerdk]

Problem solved )..perfect MajP thanx for helping me out on this!!!

 

[lespaul]

jammerdk,
You should give MajP a star for his help by clicking the link in one of his posts:

Thank MajP
for this valuable post!

Leslie

Come join me at New Mexico Linux Fest!

 

[jammerdk]

Ups of course there you go MajP

 

[SkipVought]

jammerdk,

Try that [purple]little purple star[/purple] thing again for MajP.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}