VBA Programming (1 Viewer)

[Martyh]

I have info in Excel about people staying in camp. I've show a sample of one person in the following:

How do I convey info to a Table in Access that is normalized in the
easiest manner using VBA? There must be some "standard" way -- I can't be the only
person thats come across this pattern!!

 

[GPGeorge]

Spoiler: etc

I have info in Excel about people staying in camp. I've show a sample of one person in the following:
View attachment 101271

How do I convey info to a Table in Access that is normalized in the
easiest manner using VBA? There must be some "standard" way -- I can't be the only
person thats come across this pattern!!

The beauty of Access is that you can create custom applications, but that comes with the obvious downside that each custom application is unique, hence "standard" isn't a highly likely factor in the sense that someone can point to a pile of code and say "use this".

That said, there are standard approaches. Here, I'd write a module using a series of one or more queries to append the various values from the linked Excel worksheet into the appropriate tables in the Access relational database application. The little graphics may help explain the process, but they're sort of irrelevant to the internal process here. What matters are the Column Headings and dates at the bottom of the image. Actually, that would probably be converted to a set of records in an Access table, with fields for "ArrivalDate","DepartureDate", and "ArrivalLocation", e.g. "Camp", "TrgArea", TrgArea5" and so on.

In order to be more specific, we'd also have to see the table structure in Access.

 

[The_Doc_Man]

The first thing to remember is that Access is designed for "sparse" data. (As is almost any other relational database system.) Specifically for the case you are describing, that means that if you have nothing to say about any date, there is no record for that date. Therefore the absence of dates, surrounded by non-blank records with non-blank dates, IMPLIES that the thing in question was not in use / not active during those times.

This means that you simply record the "IN" and "OUT" transitions. Persons going "IN" are not on a mission, they are staying. Persons going "OUT" are not staying, they are either totally leaving or going out in a mission. You count the "IN" transactions to get stays. NOTE that I am assuming that you don't care if a person is CURRENTLY staying (as indicated by an IN without a matching OUT.) I assume here that RES is a residence? You count these as though they were transactions.

With respect to a person, IN and OUT transactions show where they are (or are not). With respect to a residence, IN and OUT transactions show which residences are occupied (and by how many, if multiple occupancy is allowed) and which ones are unoccupied. Then the trick is to assure that you always get IN and OUT notices for everyone.

That is a HIGH LEVEL overview of what might be considered one standard approach. Is that the only way? HELL, no. Access will keep your records in whatever way you tell it to. But if you want simplicity and a commonly used treatment, that's one example.

 

[MarkK]

One pattern might be tables: Person, Res, Mission, Schedule. Tables are mostly obvious, except Schedule might look like....
tSchedule
ScheduleID (PK)
PersonID (FK) <--- one of these to be used per row
MissionID (FK) <--- and not the other
ResID (FK)
DateTime

See how each row describes a single Person/Mission/DateTime or a Pers/Res/DateTime, as the case may be?

It might also make more sense to have a PersonResSchedule and a PersonMissionSchedule. One of those tables would be...
tPersonResSchedule
PersonResScheduleID (PK)
PersonID (FK)
ResID (FK)
DateTime

Depends what other data there is, for instance a PersonMissionSchedule row might include mission results for that person, and that would differ from the structure of a PersonResSchedule row, which might store data about that person's stay at that res. But if ALL you need is the schedule, then tSchedule, above, might do.

Those are my thoughts,

 

[Martyh]

>> One pattern might be tables: Person, Res, Mission, Schedule. Tables are mostly obvious, except Schedule might look like....
>> tSchedule
>> ScheduleID (PK)
>> PersonID (FK) <--- one of these to be used per row
>> MissionID (FK) <--- and not the other
>> ResID (FK)
>> DateTime

I like your solution MarkK, (although because Res is always the same Res and I don't care about the Mission result) using your logic, I get :

tSchedule
ScheduleID (PK)
PersonID (FK)
MissionID (FK)
DateTimeIN
DateTimeOUT

tPerson
PersonID (PK)
LastName
etc..

tMission
MissionID (PK) <---- one of these can be Res, while the rest can be the mission which incidently sometimes be the same between people
MissionLocation

This is good! ... and now I see how each row describes a single Person/Mission.

How can I devy up the results from non normalized Excel to the normalized Access using VBA?

What kind of statements would I use? ... "If IsNull(![Out to Trg Area ]) Then"
... my mind is blown at the first "if" statement!!

Regards,
Marty

 

[arnelgp]

you said each activity is shown on each line.
the simplest way to do this is document every activity/movements.

(tblSchedule)
ActivityID---Person------------ActivityDate--------ActivityType-----Mission
1------------person 1----------24-feb-2021--------In---------------
2------------person 1----------01-mar-2021-------OutToTarget----Mission1
3------------person 1----------05-mar-2021-------InFromTarget--
4------------person 1----------16-mar-2021-------OutToTarget----Mission2
5------------person 1----------22-mar-2021-------InFromTarget
6------------person 1----------27-mar-2021-------Out

you can optionally save the Mission field to another table.

(tblMissionDetail)
MissionID---------ActivityID---------Mission
1------------------2------------------Mission1
2------------------4------------------Mission2