Loop a Copy of a row. (1 Viewer)

christosT · Dec 16, 2013

Hello all!!
I am new in access and vba coding so I need some help to evaluate the following issue:

I currently have a SECONDARY table with the following fields.
1. ID
2. ContactID
3. Date
4. Status
5. Area
6. Team

The MAIN table contains the following:
1. ID
2. ContactID
3. Contact Name
4. DOF
5. Phone number
6. Address
Etc etc

For each Contact I have in the MAIN table there are multiple records (one for every day) for that Contact in the SECONDARY table. New records have to be created every day to match the current condition for each of the 250 contacts in the main table. Many contacts have a static condition for a long period of time.
The issue is that I would like to create a button, when clicked, a vba code must run that will copy for each CUSTOMER the last date status for the following date utill today is reached:

Code:

A. Loop 1
      i. Loop 2
          1. Apply Filter for [ContactID] 
          2. Select the LATEST Record 
          3. Then copy * into a new Record
          4. Change the date field to equal [Date]=[Date]+1day
          5. If the date field [Date]=today's date STOP.
     End Loop 2
End Loop 1

I hope you understand my thought. I don't know if I have thought correctly about the logic of this piece of code.
Any help would be really appreciated.
Thanks in advance
Christos

bob fitz · Dec 16, 2013

I would us some code in a loop from the last date to todays date to add each record to a recordset. However, I don't see a need for all this repetitive data. Can you explain why these records would be needed.

christosT · Dec 16, 2013

bob fitz said:
I would us some code in a loop from the last date to todays date to add each record to a recordset. However, I don't see a need for all this repetitive data. Can you explain why these records would be needed.

Hello and thanks for the prompt reply.
These records would be needed because it is
Essential to know in which area every contact is located every day.
In order to do so i would need to add a new record
Every day for each of 250 contacts.
So to avoid this it is easier to have an automated
Process to copy the last record and then manually change
The area of about 20-30 contacts that actually changes.
All other contacts remain the same. These 20-30 contacts are
Different everyday.
If there is a better way to do so please advice!
I would appreciated if you could provide me with the sample code
To bring this into life.
Thanks again
Christos

bob fitz · Dec 16, 2013

....The area of about 20-30 contacts that actually changes.
All other contacts remain the same. These 20-30 contacts are
Different everyday.
If there is a better way to do so please advice!....

Surely you just need new records with todays date for these 20-30 contacts.

christosT · Dec 16, 2013

bob fitz said:
Surely you just need new records with todays date for these 20-30 contacts.

You got a point on this because i didn't explain all the details.
So let me explain further my situation.
This access database will be used in a boy scout organisation that needs a daily printed report containing the total number of contacts (members), the area each contact is located at this particular day, the team each contact is with at this particular day and some more details about they schedule.
So a daily report is needed with all of this kind of information. In addition at the end of each month an analytical report of each member is printed containing information such as "total days with this team" and "total days in this area" and some other comparison results.

Therefore for each member there has to be a daily record for "area", "team" etc.
I thought of that way of doing this because most of the 250 members will remain with the same team and at the same area for quite a few days. So it is easier to have a "duplicated" record with today's date and only change the few contacts that actually change place or team or both today.

I hope you got the point and i am sorry if I was wearing
All the best
Christos

bob fitz · Dec 16, 2013

Sorry, but I still don't see the need for the additional entries.
If you record that I am in London on 1st Dec and the next entry records me going to Liverpool on 9th Dec. You do not need a record dated 5th Dec to determine I will have been in London on that date.

christosT · Dec 16, 2013

bob fitz said:
Sorry, but I still don't see the need for the additional entries.
If you record that I am in London on 1st Dec and the next entry records me going to Liverpool on 9th Dec. You do not need a record dated 5th Dec to determine I will have been in London on that date.

Sure, but when I want to print a report showing the status(area & team number) of all 250 members at a specific date (say 14/11/2011) the members that do not have a record for that specific date won't appear at all when the filter is applied. That's why I am confused.

bob fitz · Dec 16, 2013

Sure, but when I want to print a report showing the status(area & team number) of all 250 members at a specific date (say 14/11/2011) the members that do not have a record for that specific date won't appear at all when the filter is applied. That's why I am confused.

So each member will be at the location in the record where the date is the latest that is equal to or less than 14/11/2011

christosT · Dec 16, 2013

bob fitz said:
So each member will be at the location in the record where the date is the latest that is equal to or less than 14/11/2011

Ok i understand. The other question is how will i be able to count the total days of a contact in a specific area. I mean i dont kknow the way for dealing with the gaps between.

bob fitz · Dec 16, 2013

You can use the DateDiff() function for that.

christosT · Dec 17, 2013

hmm, I am really confused. So let's say we have the following example.

Code:

Table 1
[B]ContactID            Area          Team        Date[/B]        [B]Status[/B]
[COLOR=orange]1020  [/COLOR]                 London       932       1/1/2013        1         *
[COLOR=orange]1020[/COLOR]                   Leicester     877       1/2/2013        1         *
1021                   Bristol         999       1/1/2013         2
1022                   Glasgow      452       1/1/2013         1
[COLOR=orange]1020 [/COLOR]                 Bath           999       13/2/2013        3
[COLOR=orange]1020 [/COLOR]                 Newcastle    777       18/2/2013         3

Code:

** That means as you said correctly 
that this member between 1/1/2013 and 1/2/2013 
was at London while AT 1/2/2013 went to Leicester

and I want to to 2 things.
Firstly to Filter the results just to see on my screen where the members were at 1/1/2013. I tried to do it as you mentioned but I cannot find a predifined filter to do that so I guess I need to write some code that i really cannot if i don't have some similar code to edit as I am not a programmer unfortunately.
Next, I need to be able to print a report (having fields both from this table and from the main one - to add contact name, phone numbers etc) in a daily basis as well as for specific days in the past.
Continuing, I would like to know for each member how many days was with each team as well as how many days in each location. You pointed that this can be done with the datediff() function, but when i have a lot of gaps how am i going to use this? I really don't have a clue of how to do coding on this but I am willing to learn using a bit of your help

.
Finally, I need to print (let's say monthly and yearly) reports for each member showing their activity (How many times in this area in that area, with which team etc)

I much appreciate your time and help.

bob fitz · Dec 18, 2013

Firstly to Filter the results just to see on my screen where the members were at 1/1/2013. I tried to do it as you mentioned but I cannot find a predifined filter to do that so I guess I need to write some code that i really cannot if i don't have some similar code to edit as I am not a programmer unfortunately.

Don't understand what you mean by a "predifined filter".

Next, I need to be able to print a report (having fields both from this table and from the main one - to add contact name, phone numbers etc) in a daily basis as well as for specific days in the past.

I'm sure this can be done.

Continuing, I would like to know for each member how many days was with each team as well as how many days in each location. You pointed that this can be done with the datediff() function, but when i have a lot of gaps how am i going to use this? I really don't have a clue of how to do coding on this but I am willing to learn using a bit of your help .

I'm sure this can be done with queries.

Finally, I need to print (let's say monthly and yearly) reports for each member showing their activity (How many times in this area in that area, with which team etc)

Once again, I'm sure this information can be retrieved with queries.

Loop a Copy of a row. (1 Viewer)

christosT

New member

bob fitz

AWF VIP

christosT

New member

bob fitz

AWF VIP

christosT

New member

bob fitz

AWF VIP

christosT

New member

bob fitz

AWF VIP

christosT

New member

bob fitz

AWF VIP

christosT

New member

bob fitz

AWF VIP

Similar threads

Users who are viewing this thread