How to list out the last time when a certain person was involved in a job?

  • Thread starter Thread starter Herbert Chan
  • Start date Start date
H

Herbert Chan

Hello,

I've a database, the structure of which is as follows:

Day PersonID

It means that a person with PersonID is involved in a job on a certain day.
There are usually 7 people involved in a job on each day.

I want to be able to do the following:

1. How do I get the format below:

Day PersonID1 PersonID2 PersonID3 PersonID4 PersonID5 PersonID6 PersonID7

then the next day and so forth?

2. If possible, I want to have the following format actually:

Day PersonID1 PersonID2 PersonID3 PersonID4 PersonID5 PersonID6 PersonID7
theLastDayPersonID1_is_involved_in_a_job
theLastDayPersonID2_is_involved_in_a_job
theLastDayPersonID3_is_involved_in_a_job, etc. till PersonID7, i.e., 15
fields in total on a single row

How do I do the above? I need a vba function for quesion 2, right?

Thanks

Herbert
 
Hi Herbert,

Please post back describing the work you're trying to document and the
entities involved. Then describe the results you want to get.

People will then be happy to help you nail down what you're doing and find a
good way to get each thing done.

For example it already seems that some of the entities involved are People
and Jobs. While you are also interested in the days of events, the days
come as functions of time which the database provides functions to handle.


HTH
 
Thank you for your reply.

I may have been mumbling. Each day will only have one job, which will
involve 7 people. So please just treat Day = Job.

With this extra piece of information, I hope I have made myself clear
enough.

For requirement 1, it's just for the sake of neatness, to be able to see
who's involved on a certain day on a single row.

As for the 2nd requirement, as the distribution of job isn't actually
uniform, so I will want to be able to have a look if certain people are
required to do the job more often than other people.

Thank you very much.

Herbert
 
Hi, we still need the information I asked you to give. Please explain it in
real world terms. You're trying to describe what you want as though Access
were Excel. It isn't The kind of thing you have described can be achieved
in Excel without too much thinking but with relatively high labor cost. If
you can describe what you really want we can steer you in the direction to
create a workable application.

HTH
 
Hey Larry,

Thank you for your continuous attention.

Ok. Let me tell you the real problem I have. I actually want to find out
if there's any whatsoever pattern for my local lottery. We have 7 numbers
out of 49 numbers each time.

So the pattern I want to look for is to see on average, the number of draws
that will take place before a number will appear again. Although it is
supposed to be random, I still want to see if I can get this pattern out
from the past results.

I have at the moment the data in this format:

Draw No. Number

So I want to have the data in the following format:

Draw No. Number1 Number2, etc,. LastDrawNo.Number1Appears
LastDrawNo.Number2Appears, etc.

It is also meant to be a mental challenge to see if I can get Access to
display such a summary. But this challenge is beyond me at the moment.

If it will make things easier, I can write an Excel macro to make the data
to appear as follows:

Draw No. Number1 Number2 ... Number7

Could you help out?

Thank you very much.

Herbert
 
So the pattern I want to look for is to see on average, the number of
draws that will take place before a number will appear again.
Although it is supposed to be random, I still want to see if I can get
this pattern out from the past results.
Click to expand...

To be honest, this probably falls better into a good text or list
management system like PERL or LISP, rather than a relational database
system. What about Prolog, where you can set up the rules and see if they
break?

By the way how will you know if they are random? There is the famous
story of a book of random numbers that came back from the printers, and
the mathematician commissioning the project sent it back because there
were 3 threes in a row. They reset the whole thing and showed it to the
mathematician again, who still rejected it because there weren't as many
repeating digits as expected... Proving randomness or patterning is an
extremely complex and sophisticated field.


All the best


Tim F
 
Thanks for your reply Tim.

But is it possible to get a table like I've described in my posts in Access?
I will then try to eyeball the table and see if I can get any useful
information out of it.

It can be done manually, or by a vba macro in Excel (comparing by brute
force each number in the current draw with the preceding draws until it
finds the number again and enters it in a cell next to the current draw, and
writing this brute force macro is within my capability).

And proving the randomness of the lottery is certainly not my intention. I
just want to see if there is any more certain way to try to get closer to a
winning lottery.

Thank you very much.

Herbert
 
But is it possible to get a table like I've described in my posts in
Access?
Click to expand...

Probably. But I just can't see the utility of it.
I just want to see if there is any more certain way to try
to get closer to a winning lottery.
Click to expand...

<yawn> Sorry, I don't think I can help.

Tim F
 
Back
Top