Populate a field based on a field in another record

[BigBill57]

I have a table that lists passengers on a skydiving trip. In order to make sure that the capacity of the aircraft is properly accounted for, each jumper must be listed. Also listed is a code field that tells the staff who is doing what for each jumper; the jumps are tandem jumps, where the client is strapped to a "jump master." There also may be another jumper taking pictures/movies of the client. Below is a sample from the table that the dispatcher/coordinator completes for each load on a given day. I need to populate a field that will be used later for payroll with the client's name in the same record as the "jumpmaster" and the "photographer."
Jumper Code
Kay R T1 { the client.
ALEC TM {the "Jump master" - hidden field ="Kay R"
JUSTIN P VDO {the photographer - again, hidden field= "Kay R"
James R T1 {the client
JVC TM [jump master
TaylorC T1 {client
JUSTIN P TM {jump master
JOSH VDO {photographer

This is repeated for each client all day. There may be 27 clients (or more) in a given day.

Due to the customer's needs I can't use multiple tables (which I know how to do)

Thanks for your help.

 

[DrSimon]

Hi - it's me again!
Don't understand about multiple tables, but how about this?

Jumpers_Table
Jump_ID
Jumper_Name
Jumper_Code
XRef

So there a unique ID for each Jumper. If the Jumper isn't a client, make XRef the Jump_ID for the client.

Simon

 

[BigBill57]

One more time: I CANNOT use separate tables. The customer needs this information all on one table because of the way he operates. He is NOT willing to change his MO, so I have to come up with a way to make it work. I know how to use related tables; I can't, in this case.

 

[DrSimon]

So not just one table, but the one provided? If the table just has the 2 fields Jumper and Code, there's no way you can find out who the staff member relates to.
However you talk about a "hidden" field - that's the one I'm calling XRef. This could contain Jumper_Name but you could easily have more than one Smith J on your books which is why I suggested Jump_ID as a unique identifier to the jump.

Jump_ID Jumper_Name Jumper_Code XRef
1 Kay R T1
2 ALEC TM 1
3 JUSTIN VDO 1
4 James R T1
5 JVC TM 4

If you don't have data something like that - I can't see that you can do it. If XRef contains "Kay R" what happens when you get another "Kay R" or if the same "Kay R" has two jumps on the same day with different staff?

 

[BigBill57]

Ah. I misunderstood your original question. There are more fields in the table. I full they are:
Date LoadNum SlotNum Jumper JumperCode Notes
2/1 1 1 Kay R T1
2/1 2 ALEC TM
2/1 3 JUSTIN VDO
2/1 4 James R T1
2/1 5 JVC VDO
2/1 2 1 Tim J T1
2/1 2 ANDY TM
.... and so on.

The T1 people are clients (names in lower case). The others are staff members performing a service (names in UPPER CASE). The staff members need to relate to the clients so that the PAYROLL table can be updated with the Staff member's name, the Client, and the SERVICE rendered.

The LOAD column is used to calculate the people that can be carried up in one trip (the dispatcher uses this column and SLOT column). In fact, there are 12 available slots on each load. Some of them will not be clients, and so I don't have to worry about them. Some will be non-revenue, and I DO need to worry about them, but that will be handled by the item in the CODE column.

Does this make a little more sense?

Thanks

 

[DrSimon]

That's better but not enough.
Perhaps I should have asked some background questions.
What is your relationship to the customer? You've identified yourself as a vendor - do you have any Access knowledge? Can you change the design of that table? What are you actually expecting as an output?

Why is the LoadNum blank for all but the first row in each load? They should all be the same. But even then you cannot do what you want until changes are made. Looking at Load 1, I cannot tell which client has JUSTIN and which has JVC as photographer. And I've no idea if ALEC is the TM for one or both of them. The fact that JVC is listed under James R in the table is not enough to be an association.

I'm sorry, unless you can change that table design, this is a non-starter. With 30 or so clients a day it may well be easier for you to create a simple speadsheet or revert to pen and paper!

 

[BigBill57]

Well, I'm starting to think I'm in the wrong place to get answers. I've stated a couple of times that I know how to do this with related tables, which should have told you I have Access knowledge. I understand that I'm not an expert, but you are talking down to me.
Why is LoadNum blank? because that's how they are used to doing it.
I also think that it's pretty obvious that each new client is tied to the TM and VDO people underneath the client.

But thanks for your time; I understand that you are trying to help. I am, however, rather surprised at your closing sentence, which is very insulting.

 

[Lilliabeth]

Excel has functions that you can place in a cell to display values from other cells. That sounds like what you need. With your Access knowledge, I'm sure you know that Access doesn't work that way, and probably you are aware that what you are asking might not be possible.... I am still not 100% clear in what you want, but it doesn't sound like something that is possible in an Access table.

--Lilliabeth

 

[BigBill57]

They've been using Excel, and it takes hours to do the payroll. I'm fully aware that it was a problem with the sheet the way they had it; but since I'm been programming for 35 years, I've figured out that our REAL purpose is to satisfy the client's needs, if possible, and to NOT project our own wishes and desires just to make a job easier.
It was pretty obvious that it was difficult; I just thought that perhaps someone more experience than me (because I certainly DO NOT know everything!!) might have a tip or answer.
So, we modify the table (which will NOT delight the customer) and try to sell the modification.
Thanks again, everyone, for your responses.

 

[DrSimon]

I’m sorry you felt insulted by my reply, but please look at it from my point of view. Your descriptor is ‘Vendor’ and you presented data that seemed to show no knowledge of database table structure. That being the case I assumed that there was no real technical support available and the suggestion to use Excel or pen and paper was well intended and to my mind correct in such a situation.

However you are a programmer, which clearly helps.
You have said that the table is needed to support payroll. Under normal circumstances you could just add the number of times a member of staff appears in the table but that clearly can’t be what you’re after here. You didn’t answer my question regarding output so I don’t know if you’re after a list of Staff with related Clients Or Clients with related Staff; I will assume that you mean the latter.

That table design cannot help you as there is no conventional way to correlate client and staff on a flight (where is the key?). You need to rely on process.
A) Client always precedes related staff in the table
B)Notes field for Staff contains related client’s SlotNum. Using the Name is even less robust as it would require typing in correctly and not account for more than one client with the same name on the same flight.
Each would require related code that you could write using either Access or Excel:
A) Go through table in Date, LoadNum order until you encounter a client. Collect the details of staff until you ‘hit’ the next client or EOF. If in Access write the collected data to a file in the appropriate format. In Excel you could write to a separate worksheet.
B) Go through table in Date, Loadnum, Slotnum order. Put each Client into an array with the Load Num SlotNum and when you encounter staff member in same LoadNum with that Client Slotnum update the array. When complete export the array as above.

As always these are just suggestions and I'm sure that there are other ways of approaching this problem.

Ideally you should ask the client to understand that a change in his requirements (e.g. improving payroll efficiency) should be supported by a change in table design, but I understand that that’s not on! I hope you’re being paid well for this, because there are times when I’d just walk away from situations like this. As far as I can see any solution short of a redesign will be prone to problems and complaints by the client! Good luck and I’m sorry again to have misunderstood your abilities.

 

[BigBill57]

Thanks for responding again. I don't usually advertise myself as a programmer, because I only do Access these days (would rather do FoxPro, but there's a lot less demand for it). I learned my programming back in the COBOL RPG days. So I use "vendor" now. And you're right, it doesn't sound in and of itself like a "vendor" would know anything about programming.

I took your advice, bit the bullet and talked to the client. We're modifying the database and will use lookups (which you originally suggested). His dispatcher will just have to learn the new way of doing things. After all, he did learn how to do it originally.

Again, thanks for your responses. They have helped.

Bill

 

[DrSimon]

Glad to hear it - I'm sure the client will be happy with the solution. I never needed COBOL, I was a scientist so I started with FORTRAN - not much call for that either!