Re: next record

From: Dirk Goldgar (dg_at_NOdataSPAMgnostics.com)
Date: 03/04/05 

Date: Fri, 4 Mar 2005 13:31:35 -0500

(my comments begin near the end)

"Russ" <Russ@discussions.microsoft.com> wrote in message
news:E190374E-E225-4651-8970-5B777FDAFB3E@microsoft.com
> Dirk,
>
> 1. The SQL of the recordsource is: SELECT Demographics.LastName,
> Demographics.FirstName, Demographics.SSN, Demographics.DOB,
> Demographics.Age, Demographics.Sex, Demographics.Attending,
> New_Visit.LastName, New_Visit.SSN, New_Visit.DateOfVisit,
> New_Visit.VisitLocERClinicFloorECF, New_Visit.Side,
> New_Visit.Location, New_Visit.Joint, New_Visit.TypeOfInjury,
> New_Visit.TimePain, New_Visit.Pain_DWMY FROM Demographics INNER JOIN
> New_Visit ON Demographics.SSN = New_Visit.SSN ORDER BY
> Demographics.LastName;
>>
>> 2. The name of Table 1 is Demographics. Fields are LastName,
>> FirstName, SSN (this is the primary key), DOB, Age, Sex, Phone,
>> Address, City, State, Zip
>
> Table 2 is named New_Visit. The Fields are LastName, SSN (primary
> key), DateOfVisit (primary key), VisitLocERClinicFloorECF, Side,
> Location, Joint, TypeOfInjury, demographics, Allergic, Injury,
> TimePain, Pain_DWMY, and several others that are not used in the form.
>>
>> 3. In Demographics : no fields are indexed
>
> In New_Visit : LastName and SSN are indexed such that duplicates are
> OK.
>
> 4. Table 1 the SSN is the primary key. Table 2 (New Visit) SSN and
> DateofVisit are the primary key.
>
>>
>> 5. Referential integrity is enforced for cascading updates and
>> deletes.
>>
>> 6. Demographics and New_Visit are related by SSN. Demographics is
>> on the 1 side and New_Visit is on the many side.
>
> Although lastname is not related, when i enter the record in teh form
> i need the last name and SSN to go to both tables.

If SSN is the primary key of Demographics, and that table contains the
LastName field, why would you also have LastName as a field in
New_Visit? That would be redundant. A query like this:

    SELECT
        Demographics.LastName,
        Demographics.FirstName,
        Demographics.DOB,
        Demographics.Age,
        Demographics.Sex,
        Demographics.Attending,
        New_Visit.SSN,
        New_Visit.DateOfVisit,
        New_Visit.VisitLocERClinicFloorECF,
        New_Visit.Side,
        New_Visit.Location,
        New_Visit.Joint,
        New_Visit.TypeOfInjury,
        New_Visit.TimePain,
        New_Visit.Pain_DWMY
    FROM
        Demographics
    INNER JOIN
        New_Visit
    ON Demographics.SSN = New_Visit.SSN
    ORDER BY Demographics.LastName;

will automatically pull up the LastName from Demographics that
corresponds to the SSN you enter for New_Visit. If the purpose of your
form is to view and enter visits, then there's really no problem. The
only thing this won't let you do is enter a visit for a new patient who
doesn't have a record in Demographics. But for that you really should
open a form based on Demographics, enter the patient and close the form
or save the record, and then return to this form to enter visits.

> Also concernign teh age. I want to enter the Date of Birht, DOB, and
> have the age calculated. It is my understanding that a calculated
> value should not be stored.

Right.

> However, this data that is entered and
> sent to the Palm Pilot. So I need the value, age, to be in a table to
> go to the palm. i have a code taht calculates this value on the
> click of the age field. is tehre a way to have this recalcuated when
> the form is opened or when the persons age changes, everyyear?

Is there some reason you can't send the results of a query to the Palm
Pilot, instead of a table? Calculations like this should be made in a
query. For most purposes, a query can be substituted wherever a table
can be used. I don't know about exporting to a Palm Pilot, though. If
you really *have* to export an actual table, then I'd set up code to
stick the query results (including the calculated age) into a temporary
table, and then export that temporary table to the Palm Pilot.

Of course, that means the Palm Pilot's data could become out of date,
but that's a separate problem. 

-- 
Dirk Goldgar, MS Access MVP
www.datagnostics.com
(please reply to the newsgroup)

Relevant Pages

  • RE: IIf statement vs Select Case statement
    ... in a VBA function which the query calls. ... To create a function called AgeGroup go to the VBA window by pressing Ctrl+G ... parentheses so it accepts the age value. ... so in Access you can't count individual patients this ...
    (microsoft.public.access.queries)
  • Re: IIf statement vs Select Case statement
    ... To compute the age from the date of birth you can add a simple function like ... You can call this in a query once you've added it to a module in your ... get a distinct count of the patients. ... which would allow you to get the age groups by joining ...
    (microsoft.public.access.queries)
  • Re: formatting a calculated number field on a form
    ... I calculate 4 "ages": age at surgery, age at heart attack, age at ... This data could easily be tracked as a flat-file in Excel, ... >INSERT INTO Test (key_col, DoB, DoD) ... >Query object and save it manually: ...
    (microsoft.public.access.forms)
  • RE: Query return value is Null
    ... Age Gender Age ... Since you're using a Totals query (a query that uses ... be any display of data at all. ... Dim Rst As DAO.Recordset, strMsg As String ...
    (microsoft.public.access.queries)
  • RE: Query return value is Null
    ... Age Gender Age ... Since you're using a Totals query (a query that uses ... be any display of data at all. ... Dim Rst As DAO.Recordset, strMsg As String ...
    (microsoft.public.access.queries)