473,320 Members | 2,162 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Transferring cells to a new field, but shifting the down one row

Hi,

I have created a test table with 10 different codes in 10 different cells (image below) in a field.

The goal is to develop a code that creates a new field where the cells in the previous field are shifted down and the cells that were previously at the very bottom are shifted up to the cell.

The code I have so far can create the new field, but I have no idea how to make it so the pattern of cells shift down from field1 to the new field as shown in the image.



Expand|Select|Wrap|Line Numbers
  1. Private Sub Command0_Click()
  2.  
  3. Dim curDatabase As Database
  4. Dim tblTooAddToo As TableDef
  5. Dim FieldTest As Field
  6.  
  7. ' Get a reference to the current database
  8. Set curDatabase = CurrentDb
  9.  
  10. ' Get a reference to a table named TestTable - NOTE: The table MUST exist
  11. Set tblTooAddToo = curDatabase.TableDefs("Table1")
  12.  
  13. 'define the fields using the CreateField method
  14. ' Syntax: .CreateField("FIELD NAME", TYPE, [Length])
  15. ' I used the predefined types already in Access
  16. Set FieldTest = tblTooAddToo.CreateField("", dbText, 12)
  17.  
  18. 'use the APPEND method to add the fields to the table
  19. With tblTooAddToo.Fields
  20. .Append FieldTest
  21. End With
  22.  
  23. End Sub
Please let me know if you have any ideas.

Thank you!
May 17 '17 #1

✓ answered by PhilOfWalton

Here is a Micky Mouse Db that may give you a start.

Using the Employee form, you have to set up the first week's Schedule and sequnece (This is the order in which the employees are processed (It won't be alphabetical as in your example)

I have allowed for 7 days, so on the Form 1, if you select Day 1 (Monday?), it will create a shifted list for the following Monday and so on. So do that for days 1 to 6.

The results can be seen in Query1_Crosstab. Have a look at that without modifying the database. Then Use form 1 to add the next week's times and you will see how it progresses.

It takes a bit of time to set up the Employees Week 1 times, so if you manually delete any records from the TblJoinEmpTimeSlots, don't delete any records where the day is from 1 to 7. That's the base for the next weeks calculation.

As I say it's a possible starting point. I haven't looked at new employees or changes to someones schedule.

Hope that helps

Phil

8 1068
PhilOfWalton
1,430 Expert 1GB
I presume the code you have posted creates the new field successfully.

I think in order to accomplish what you want you are going to need to define a sort order, and whilst it is alphabetical in Field1, it no longer is alphabetical in field2.

Are you intending to continue the process to add Field3 based on Field2, if so where do you stop.

I must say it's an unusual question and a little background information on what you are trying to achieve might help.

Phil
May 19 '17 #2
Hi Phil,

Thank you for taking the time to reply to my odd request .

I would like to create a button on a form that continues this process each time I click it.

So yes - once the cells are shifted done from field 1 to field 2 I would like to be able to shift field 2's cells down one again and have the bottom cell shifted to the top of field 3.

The reason I am trying to figure this is out is because I will be creating a staff schedule for a week, and to be fair, I want the schedule (new field) for the following week to shift down one cell and have the bottom cell shift to the top of the new field created.

The code below works to create a new field if you have a table named "TableTest":
Expand|Select|Wrap|Line Numbers
  1. Private Sub Command0_Click()
  2.  
  3. 'Dim curDatabase As Database
  4. 'Dim tblTooAddToo As TableDef
  5. 'Dim Day1 As Field
  6. 'Dim Day2 As Field
  7. 'Dim Day3 As Field
  8. 'Dim Day4 As Field
  9. ''Dim Day5 As Field
  10.  
  11. ' Get a reference to the current database
  12. 'Set curDatabase = CurrentDb
  13.  
  14. ' Get a reference to a table named TestTable - NOTE: The table MUST exist
  15. 'Set tblTooAddToo = curDatabase.TableDefs("Table1")
  16.  
  17.  
  18. 'define the fields using the CreateField method
  19. ' Syntax: .CreateField("FIELD NAME", TYPE, [Length])
  20. ' I used the predefined types already in Access
  21. 'Set Day1 = tblTooAddToo.CreateField("Day1", dbText, 12)
  22. 'Set Day2 = tblTooAddToo.CreateField("Day2", dbText, 2)
  23. 'Set Day3 = tblTooAddToo.CreateField("Day3", dbDouble)
  24. 'Set Day4 = tblTooAddToo.CreateField("Day4", dbText, 10)
  25. 'Set Day5 = tblTooAddToo.CreateField("Day5", dbText, 10)
  26.  
  27. 'use the APPEND method to add the fields to the table
  28. 'With tblTooAddToo.Fields
  29. '.Append Day1
  30. '.Append Day2
  31. '.Append Day3
  32. '.Append Day4
  33. '.Append Day5
  34. 'End With
  35.  
  36. Dim curDatabase As Database
  37. Dim tblTooAddToo As TableDef
  38. Dim FieldTest As Field
  39. 'Dim Day2 As Field
  40. 'Dim Day3 As Field
  41. 'Dim Day4 As Field
  42. ''Dim Day5 As Field
  43.  
  44. ' Get a reference to the current database
  45. Set curDatabase = CurrentDb
  46.  
  47. ' Get a reference to a table named TestTable - NOTE: The table MUST exist
  48. Set tblTooAddToo = curDatabase.TableDefs("TableTest")
  49.  
  50.  
  51. 'define the fields using the CreateField method
  52. ' Syntax: .CreateField("FIELD NAME", TYPE, [Length])
  53. ' I used the predefined types already in Access
  54. Set FieldTest = tblTooAddToo.CreateField("FieldTest", dbText, 12)
  55.  
  56.  
  57. 'use the APPEND method to add the fields to the table
  58. With tblTooAddToo.Fields
  59. .Append FieldTest
  60. End With
  61.  
  62. End Sub
Thanks again.
May 20 '17 #3
PhilOfWalton
1,430 Expert 1GB
Sorry, I'm probably being a bit thick, and , if I may, I'm going to have to probe a bit deeper into what you are trying to achieve form the staff point of view, NOT the program point of view.

So say you have 12 staff, lets call them A,B,C,D,E,F,G,H,I,J,K,L.
It would appear from your code that they do a 5 day week.
Do all of then work every day, or do say A,G & L work on Monday of the first week and the rest have the week off, then B,E & I work on Tuesday of the following week, and everyone else has all the other days off?

That doesn't sound very likely, so hopefully that scenario is wrong.
Could you mean that on Week 1, A,G & L have to get in early to unlock, and on week 2, B,E and I have to get in early?

I think, it would be helpful if you sent a "table" (perhaps a spreadsheet) to show how your work schedule is laid out.

You may be on the right track, but my gut feeling is more in the lines of a Staff table and some sort of pointer as to which week they are "On Call"

Phil
May 20 '17 #4
No worries at all Phil - I thought it would be best to not go too in depth so that's my fault. I am very grateful that you took the time to reply at all.


REG-O refers to a day off, and when Saturday cell is empty it means the employee is not scheduled.

So this is an example of one work week. I was trying to develop a code where I can create 6 new fields, and just shift the cells from the previous 6 fields down so that eventually everyone would have worked the same shift pattern.

To better explain - for the next week starting Monday, May 15th: Alexis will have Albert's shifts from the previous week, Bernie will get Alexis' shifts from the previous week.... and finally - Albert will get Terri's shifts from the previous week. The for the week starting May 22nd, the shifts would rotate again in the same way if I were to click the button.

Example of rotation from previous month:



Thank you.
May 20 '17 #5
PhilOfWalton
1,430 Expert 1GB
Thanks for the info, that now makes perfect sense.

Give me a few days to look at it, and I will come back early next week. I think I can see a much neater solution, but I want to check it out

Phil
May 20 '17 #6
zmbd
5,501 Expert Mod 4TB
This isn't a normalized database structure...
https://bytes.com/topic/access/insig...ble-structures
which may be adding to your frustrations.
May 22 '17 #7
PhilOfWalton
1,430 Expert 1GB
Here is a Micky Mouse Db that may give you a start.

Using the Employee form, you have to set up the first week's Schedule and sequnece (This is the order in which the employees are processed (It won't be alphabetical as in your example)

I have allowed for 7 days, so on the Form 1, if you select Day 1 (Monday?), it will create a shifted list for the following Monday and so on. So do that for days 1 to 6.

The results can be seen in Query1_Crosstab. Have a look at that without modifying the database. Then Use form 1 to add the next week's times and you will see how it progresses.

It takes a bit of time to set up the Employees Week 1 times, so if you manually delete any records from the TblJoinEmpTimeSlots, don't delete any records where the day is from 1 to 7. That's the base for the next weeks calculation.

As I say it's a possible starting point. I haven't looked at new employees or changes to someones schedule.

Hope that helps

Phil
May 22 '17 #8
Thank you all for taking the time to help. I will work with the database you created for me Phil and go from there.
May 22 '17 #9

Sign in to post your reply or Sign up for a free account.

Similar topics

2
by: ehm | last post by:
I am working on creating an editable grid (for use in adding, deleting, and editing rows back to an Oracle database). I have a JSP that posts back to a servlet, which in turns posts to a WebLogic...
0
by: Rick Slansky | last post by:
Hi: This should be a fairly simple page, which lines up the second row cell nicely in the lower right corner. It does in Netscape, but when I check the page in Explorer, that cell (the entire...
5
by: Geoff Portbury | last post by:
I'm a swimming club coach. I have a data base for recording times at various meets. I have a FORM for entering times called SWIM TIMES. When I enter the swimmers name ID, eg FOR01 for Doug Ford I...
3
by: C Villalba | last post by:
Birth Date and a Purchase Date aata is being stored in sql. This information is retrieved across time zones through a .Net Web Service. When the data is viewed in a .Net client application these...
4
by: G | last post by:
Hello, I have a "two page form" - Page1.aspx and Page2.aspx. I have a function in my Page1.cs file called Button_Click. A simple version: ************************ if (Page.IsValid) {
1
by: vinitfichadia | last post by:
Hello Pals, In VB.Net, I am having textbox which has autocomplete source of 100 strings, i want feature in which, the list of 100 strings drops down once the specific textbox gets the focus. ...
6
by: tcomer | last post by:
Hello, I have a pretty interesting problem here.. Ok, I have an integer that needs to be right shifted, and then converted to a char.. which is then is used to build a string. Heres an...
2
by: plaguna | last post by:
This is the scenario. I have a table “Main” with 2 fields: Location and Salesperson (actually it has several fields but I want to concentrate on these two fields. Also, I have a table called...
6
by: Palehorse | last post by:
I'd like to apologize upfront for me saying "I'm not a programer", I'm sure you all hear this a hundred times a day. Unfortunately, in this case, it's true. I've been working on trying to figure out...
4
by: Cady Lane | last post by:
I can't figure out why my img keeps moving everything down. I have worked on this for a solid 24+ hours and still cannot position this logo. This is the last step. Please help! The css is:...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.