One to Many Query Issues

[JonnyRotten]

I have a database I am creating that has two tables with a one to many
structure with Ref Int Forced.
For ease of typeing I will call them TBLA and TBLB
TBLA has:
ID (key field autonumber)
Name
Date
Time

TBLB has:
ID (Key field number, linked to ID in TBLA)
Activity (the important data I need to collect goes here)
ValueAdded
Comment

Every hour I have a form that they will fill out. The main form is from
TBLA, the subform is from TBLB and each hour they can have up to 4
enteries in the subform tied to the timed entry in TBLA

If nothing is entered after 50 minutes I have it fill out the Activity
field with "away from desk"

Now I need to make it so that they can go back at the end of their day
and edit the Away from desk enteries and only see those.
I have tried doing this two ways, Making two queries that are linked
with the ID field, where QueryB pulls all the enteries with "away from
desk" in them and QueryA (at least in theory) had the ID field with a
like statement that pointed to the ID field in QueryB. This approach
just generates a error every time I run it asking me what ID field Iw
ould like to query for. If I leave out the Like statement in QueryA
then I get QueryB pefect, and QueryA pulls every entry out of TBLA.
this is a problem because when I make a form from this query I get
enteries in it that are blank and will confuse the user.

Method 2. Made 1 query which included both tables. The query works
great. I get all the enteries with "away from desk" in them. But when I
make the form for them to edit the enteries, I can only replace the ONE
"away from desk" activity field, and I cannot add more activities to
the current time stamp.


I'm stumped at this point. Is there a better way I should be doing
this?

 

[[MVP] S.Clark]

I would create a query called qryAwayFromDesk. It would return any unique
tblA.ID where there existed a tblB.Activity = "Away from Desk".

I would then link qryAwayFromDesk to tblA in another query, and use that for
the basis of the End of Day correction process.

So, your form could open with either tblA or qryAwayFromDesk as the
recordsource. You can use the filter property of the OpenForm method to
assign the desired value.


--
Steve Clark, Access MVP
FMS, Inc
http://www.fmsinc.com/consulting
Professional Access Database Repair
*FREE* Access Tips: http://www.fmsinc.com/free/tips.html