Random number generator

[Lifeseeker]

Hi,

exploring a concept here.

In order to maintain the uniqueness of each record, I am thinking to have sone kind of random number generator built in or to be built outside access program for people to use for data entry.

I would like to generate say...a 9digit number everytime it is asked to run.

Is this a vba programming issue or? I am relatively new to it.

I guess ye more digit it can generate the better and the lesser than tha it gets used more than once.

Any thought and feedback is much appreciated.

 

[PNGBill]

A Random Number can be repeated no matter how many digits it has.

Use AutoNumber and set Duplicates to not be allowed.

As long as Duplicates are set to not be allowed you shouldn't have an issue with numbers repeating.

How to get the number??

many options here. Most common is Auto Number but that can have issues with being unable to be edited should you not be happy with the number.

You could write code to create a number related to the Date and Time and Operator ID and then the number should be duplicated as the same operator can not create two records at the same time but if the number is not important except to be Unique then this will be a waste of time.

 

[plog]

For uniqueness you use an autonumber, for random you use Rnd() and Randomize(). How exactly is your system to work and why do you need a number both random and unique?

 

[Lifeseeker]

For uniqueness you use an autonumber, for random you use Rnd() and Randomize(). How exactly is your system to work and why do you need a number both random and unique?

Hi,

thanks for reply.

I am currenly using an autonumbwr for yhe pk and have set one of yhe fields to be unique and no duplicates in the record table.

The field set to no duplicates represents each patient uniquely and each patient will have a unique number to represent himself.

Due to project requirement, we want to also store patient info if the same guy shows up in the hospital. So if that field is set to no duplicate, we won't be able to store him in the database for the second time.

So I thought of this random number generator yhat can be used. So if it works, the random number can be used to represent each patient in place of the real patient Id(users will still enter yhe patient id,but will enter yhe new randomly generated number as well) So even though the same patient that shoews up at the hospital?we can still store him in the system.

 

[Lifeseeker]

So essentially we are saying.....even thought this is the new patient, but we want to store him as a new or different patient, or record for that matter.

 

[stopher]

I'm a bit lost with your patient scenario.

But it sound like you need another table - tblHospitalVisits (related to Patient)?

I'm guessing a bit because I don't really understand your scenario.

Chris

 

[plog]

I'm with stopher--you need two tables--Patients and Visits. You would keep unique patients in Patients (this is where PatientID would be created) and then unique visits in Visits (this would get the PatientID from the Patients table and use it to identify which patient had the visit). That way a person would appear in Patients one time and could appear in Visits multiple times.

 

[Lifeseeker]

I'm with stopher--you need two tables--Patients and Visits. You would keep unique patients in Patients (this is where PatientID would be created) and then unique visits in Visits (this would get the PatientID from the Patients table and use it to identify which patient had the visit). That way a person would appear in Patients one time and could appear in Visits multiple times.

Later on when we do the analysis, we only want to look at the latest patient visit and results.

So if patient A had a visit two years ago, we don't want to look at the treatment done around that time. We only want to look at the latest. Although the visit table can store multiple instances of patient A, but we only want to look at the latest.

So I'm not sure if having another table is okay. Or is there a way around it?

Any comment/thought is much appreciated.

 

[plog]

There's not a way around it--its part of the system. The two table structure is ideal for this.

To get the latest visit you run a Aggregate query like this:

SELECT MAX(VisitDate) AS LastVisit, PtID FROM Visits GROUP BY PtID;

Then you can use that query as a subquery to pull the Patient information (linking the PtID to the ID in the Patient table) and to pull in the full visit information (linking LastVisit to VisitDate and PtID to PtID.

 

[Lifeseeker]

There's not a way around it--its part of the system. The two table structure is ideal for this.

To get the latest visit you run a Aggregate query like this:

SELECT MAX(VisitDate) AS LastVisit, PtID FROM Visits GROUP BY PtID;

Then you can use that query as a subquery to pull the Patient information (linking the PtID to the ID in the Patient table) and to pull in the full visit information (linking LastVisit to VisitDate and PtID to PtID.

Hi,

I have tried it, but it's not exactly working the way I intended. I can give you an example:


In the visit table, I have

visit_number patient_id visit_date
123 abc 2011 9 20
456 bcd 2011 7 6
675 bcd 2011 9 10
573 gdf 2011 7 8
745 gdf 2011 7 9
When I ran the query, the code is shown below:

SELECT visit.patient_id
FROM visit
WHERE (((visit.visit_date)=(select max(visit_date) from visit)));

patient_id is the look up from the patient table.

I only get

123 bcd 2011 9 20


For each different patient, we would like to show his most recent visit.

So basically the algorithm becomes......we want all patients to be extracted, but only their most recent visit.

So we would like the output to be....

123 abc 2011 9 20
675 bcd 2011 9 10
745 gdf 2011 7 9

A patient can have multiple visits, but we want only the most recent visit.

Is this possible? It seems to be a much complicated situation than before...

Any thought/comment much appreciated.

 

[plog]

You forgot a GROUP BY clause and to link your Patient IDs. This code should give you want you want:

SELECT visit.*
FROM visit INNER JOIN (SELECT visit.patient_id, Max(visit.visit_date) AS lastvisit
FROM visit
GROUP BY visit.patient_id) AS subvisit ON (visit.visit_date = subvisit.lastvisit) AND (visit.patient_id = subvisit.patient_id);