Trouble making query from drop down box on a form

[david.paton]

I have a database in which widows are looked after by legatees. There can be multiple widows assigned to a legatee but one widow may only have one legatee.

There is a widow table and a legatee table. It then has a table joining the two, that has the legatee ID, widow ID and a seperate ID field.

I have a form that shows the widow information and where you can update it or edit it. There is a combo box on that form that shows the legatee that is assigned to the widow. You can also use this drop down box to choose the legatee that is assigned to the widow, should it change. I want to be able to view the widow form, see the legatee assigned to the widow and press a button that queries the database to tell me what other widows the legatee is assigned to. I have tried to make a query that has the widow ID, legateeID and the widows name but that didn't work. I am not sure what to do.

Thanks,
Dave

 

[sneuberg]

This should be fairly easy. Try creating a query using the query builder. If you have the relationships in place the tables in your query will already be joined. Just start by putting all of the fields in the result. For the criteria of the legatee id it would be something like:

Forms![The Name of the Form]![The name of the legette combo]

if you can't get it working post the SQL of what you have.

 

[david.paton]

Just had a thought, I will post my db up here so you can see what I am looking at. Also, I have a table that maps the joins between the widowID and LegateeID, but the table has nothing in it, even though there are several widows that have been joined to legatees. Should there be data in this table as there are several joins?

I will post my database now as I have to leave soon and I will get a chance to look at it again later.

 

[david.paton]

I tried what you suggested, used a query with the criteria for LegateeID being [Forms]![Widow]![cmbWidowLegatee] and just included all the fields and that partly works. There is only two widows in the table at the moment and they are both joined to different Legatees, therefore, the query should only show one result when running the query but it shows 3 results using the legatee of terry as an example.

 

[sneuberg]

In the database you uploaded there's nothing in the LookAfter table so the query doesn't produce any results. What you will see depends on what's in the junction table. I don't see how Legatees and Widow are assigned to this table but the presence of this table implies a many to many relationship between Legatees and Windows. On the other hand I see that this is restricted to a one to many relationship by a unique index on the LAWIdowID field in the LookAfter table.

I suggest you correct the structure in this database before proceeding. If the relationship between Legatees to Widows is one to many then get rid of the LookAfter table. This table will make things a lot more complicated than they need to be.

 

[Grumm]

Change the query WhichOthers with this :

SELECT Widow.WidowName, Widow.WidowLegatee, Legatee.LegateeName
FROM Widow left join Legatee On Widow.WidowLegatee = Legatee.LegateeID
WHERE (((Widow.WidowLegatee)=[Forms]![Widow]![cmbWidowLegatee]) AND ((Widow.WidowId)<>[Forms]![Widow]![WidowID]));

You can delete the table "LookAfter". You don't use it...

Edit,
I forgot to tell you that i added a field with the WidowID and made it invisible.

 

[moke123]

while i agree with the other suggestions, i cant help wonder if there is a chance that a legatee will change over time thus necessitating the need for a junction table and likely a few extra fields to indicate the period of time they held that position. should there ever be an impropriety it may prove important to have a history. (sorry i'm a criminal investigator, just the way my mind works)

 

[Grumm]

Well yes you can add a table to store the history. But that will not be a function table in that case.
It will just be a table with an unique ID and 2 FK : WidowID and LegaleeID.

He just wants a list of other Widow's that have currently that legalee. In my query I excluded the widow that the user see in te form. If you need to see that record also, just remove "AND ((Widow.WidowId)<>[Forms]![Widow]![WidowID])"

 

[moke123]

I suppose it all depends on the OP's requirements. A history table is certainly not required. Adding a beginning and end date to the LookAfter table would serve this purpose. As previously noted the OP's data structure needs some tweaking and my initial thoughts are that the Widows table should only contain attributes of the widow and not who is currently assigned as their legatee as this appears fluid and could change over time.

 

[Grumm]

I agree on that point. His database is not normalised. But when a working query he can tweak the database and modify the sql to fit his new design.

 

[david.paton]

I forgot to tell you, a widow can be assigned to a different legatee over time. An example of this is a legatee might die or get very ill preventing him from performing his duties.


There is also a section at the bottom of the widow form that allows for comments to be made by different Legatees regarding widows, for instance, a legatee not assigned to a given widow, may want to add notes on the widows file for other people to see. An example of this might be that Mrs Brown has gone on holidays for 2 weeks. It has room for the date and what legatee recorded it. This is recorded in table format so people can scan down and see the notes that have been added on the account, the date and who by.


Regarding the design of my tables, I have a field in the Widow table called WidowLegatee just to record the Legatee assigned to the widow. I don't know how to code in sql and I can build databases in access but I don't know a great deal, so I have been using the combo box wizard a lot.

I select the option to get the values from another table or query and I was choosing the legatee table, then selecting the legatee ID and name in no particular order. I then created a field in the widow table just to store the value of the combo box as at the next window after selecting to hide the key, asks you where to store the information. The drop down list only showed fields in the widow table and I didn't know how to get the option of storing the value in the lookAfter table. Creating a field in the widow table solved this solution for me but I know it is not good database design.

 

[Grumm]

So regardless of the design of the database, did you get the query working ?

 

[moke123]

I forgot to tell you, a widow can be assigned to a different legatee over time. An example of this is a legatee might die or get very ill preventing him from performing his duties.

then you will need a junction table such as your lookafter table with fields added to indicate when they began and ended their duties. you would then find the current legatee as the one whose end date is null. you could simply use a subform using the widowID as the master/child links.

 

[david.paton]

I put this code from Grumm into my query but it still gives me no results when I run it. No errors but no results.

SELECT Widow.WidowName, Widow.WidowLegatee, Legatee.LegateeName FROM Widow left join Legatee On Widow.WidowLegatee = Legatee.LegateeID WHERE (((Widow.WidowLegatee)=[Forms]![Widow]![cmbWidowLegatee]) AND ((Widow.WidowId)<>[Forms]![Widow]![WidowID]));

 

[moke123]

iirc, you wanted all widows that a legatee had but not the one currently selected.

i tried this and it seemed to work. Had to add more records to the example you posted so that there were multiple widows assigned to a legatee so it could return records.

SELECT Widow.WidowName, Widow.WidowLegatee, Legatee.LegateeName, Legatee.LegateeID, Widow.WidowID
FROM Widow LEFT JOIN Legatee ON Widow.WidowLegatee = Legatee.LegateeID
WHERE (((Widow.WidowLegatee)=[Forms]![Widow]![WidowLegatee]) AND ((Widow.WidowID)<>[Forms]![Widow]![WidowID]));

 

[david.paton]

Thanks for that Moke, it worked perfectly. Is there a way of doing this without the use of sql as I would really like to learn it, or will I need to learn sql if I really want to learn how this is done?