Well its been a while since I played with Queries so some noob help please.

[jam]

Well I haven't played with Access since V2 and although queries don't seem to of changed much I'm affraid my memory retention hasn't fair as well.

I'm constructing a query based on 2 tables, Owners and Ownersnotes
Owners 1------oo Ownersnotes.
My relationships are ok as they work perfectly in my form. I've added both tables into my query and I want to view a record say Mr Joe Bloggs (done via parameters being set calling for a dialog box) then I want to display all the notes made on Mr Joe Bloggs. When I add the ownersnotes table into the query I do not get any results. Once I've removed it I retreive the correct results.

As I said its been a while and I'm sorry for being such a noob and probably making the most simple of mistakes. Any help or insight would be great.

Cheers
jam

 

[WIS]

Well I haven't played with Access since V2 and although queries don't seem to of changed much I'm affraid my memory retention hasn't fair as well.

I'm constructing a query based on 2 tables, Owners and Ownersnotes
Owners 1------oo Ownersnotes.
My relationships are ok as they work perfectly in my form. I've added both tables into my query and I want to view a record say Mr Joe Bloggs (done via parameters being set calling for a dialog box) then I want to display all the notes made on Mr Joe Bloggs. When I add the ownersnotes table into the query I do not get any results. Once I've removed it I retreive the correct results.

As I said its been a while and I'm sorry for being such a noob and probably making the most simple of mistakes. Any help or insight would be great.

Cheers
jam

From what you are saying you have a PK OwnerID in the Owners and FK OwnerID in the Ownersnotes and you have joined OwnerID in the 2 tbls - is that correct?

I assume in your form you have a main form (Owners) and a subform (Ownernotes) where many Ownernotes can show per Owner.

 

[jam]

Yep you've got it. Shown using a subform in a kind of data sheet view. Very similar to the Contact Notes access template, except for its not a linked form but a subform. Think I've sorted this now. However I now have another issue.

I get results from the query no problem at all. So I've built a form so that people can edit the data and add notes to someones file ect. However the results are displayed fine and dandy first of all asking for the surname via a dialog box and results displayed. But I may have 3-4 people with the surname smith and when I want to scroll through the results to find the correct Mr Smith every time I try to move forward a record I yet again see the dialog box asking for the surname. If I press escape it moves on to the next record but the subform with the notes does not change.

 

[WIS]

Yep you've got it. Shown using a subform in a kind of data sheet view. Very similar to the Contact Notes access template, except for its not a linked form but a subform. Think I've sorted this now. However I now have another issue.

I get results from the query no problem at all. So I've built a form so that people can edit the data and add notes to someones file ect. However the results are displayed fine and dandy first of all asking for the surname via a dialog box and results displayed. But I may have 3-4 people with the surname smith and when I want to scroll through the results to find the correct Mr Smith every time I try to move forward a record I yet again see the dialog box asking for the surname. If I press escape it moves on to the next record but the subform with the notes does not change.

I assume that you have a PK OwnerID, Autonumber. Are you using a cboBox for the names - if so set the RowSource as OwnerID, Person, "some other identifying feature" and the ColumnWidths as 0;x etc.

When you drop down the person, you won't see the ID but you can navigate to the correct person on the "some other identifying feature" fld.

 

[jam]

I assume that you have a PK OwnerID, Autonumber. Are you using a cboBox for the names - if so set the RowSource as OwnerID, Person, "some other identifying feature" and the ColumnWidths as 0;x etc.

When you drop down the person, you won't see the ID but you can navigate to the correct person on the "some other identifying feature" fld.

Table - Owners
OwnersID (Autonumber) 1
Forename
Surname
Address
ect

Table - OwnersNotes
OwnersNotesID (Autonumber)
OwnersID oo
Date
time
Note (memo)

I'm running the query with all the above fields but a requirement on the owners Surname. The results work perfectly on the query alone. But if Mr A Smith has 2 notes on his file then Mr A Smith appears twice.

Now I've based a Form on the results from the query. In this form I've decided instead of having 2 records for Mr A Smith I'll create a Subform to show all instances of Ownersnotes. So I can view his record and all his notes on the 1 page. But everytime I try to move forward onto Mr B Smith I get the query asking for the surname again.

Now this is my opinion and I could be way of track here but could I get rid of the Ownersnotes table entirely from the query and retrieve the information on Mr A Smith based on the Owners query then insert a subform using the ownersnotes table rather than a query, link master and child by the OwnerID number?

 

[jam]

Now this is my opinion and I could be way of track here but could I get rid of the Ownersnotes table entirely from the query and retrieve the information on Mr A Smith based on the Owners query then insert a subform using the ownersnotes table rather than a query, link master and child by the OwnerID number?

That works a treat!