Using Lookup to select table values

[RogerMcElroy]

polo system design.pdfI've attached the database design. In the table called ActivityTeamPersonHose, I am trying to lookup names for the fields ActivityID and HorseID from the respect tables to which the ATPH table is linked. It is currently bringing back the ID numbers correctly but I want it to bring back the names.

The code on the lookup is as follows:

SELECT [Activity].[ID], [Activity].[ActivityName] FROM Activity ORDER BY [ActivityName]; - this brings back the Activity ID



and

SELECT [HorsesCurrentlyatDruids].[ID], [HorsesCurrentlyatDruids].[HorseID] FROM HorsesCurrentlyatDruids ORDER BY [HorseID]; - this brings back the Horse ID

In both cases the table which it brings the values back from joins on another table to find the names and sadly, my SQL skills are not at the level where I can work this out.

Any assistance would be gratefully received.

[CJ_London]

Your description sounds suspiciously like you are using lookups in tables - they cause confusion like this

in a sql query if you want to bring back the name, you need to join to the table where the name is.


as a rowsource to a combo box I don't see any issue with your activity sql - just set the first columnwidth to 0 and set column count to 2

for your horsename it sounds like what you need is

SELECT [Horse].[ID], [Horse].[HorseName] FROM Horse ORDER BY [HorseName]

but not sure why you have that field specified in that table since it is already specified in HorsesCurrentlyatDruids

[Micron]

I agree with the table level lookups comment. I think that if you insist on sticking with it, you need to specify the .Value property of the lookup field, as in
[Activity].[ActivityName].Value

Not 100% sure since I don't use table level lookups either.

[RogerMcElroy]

Your description sounds suspiciously like you are using lookups in tables - they cause confusion like this

in a sql query if you want to bring back the name, you need to join to the table where the name is.


as a rowsource to a combo box I don't see any issue with your activity sql - just set the first columnwidth to 0 and set column count to 2

for your horsename it sounds like what you need is

SELECT [Horse].[ID], [Horse].[HorseName] FROM Horse ORDER BY [HorseName]

but not sure why you have that field specified in that table since it is already specified in HorsesCurrentlyatDruids

First of all - thank you for your reply and yes, I am using lookups in table to limit the later entry of the data into those tables.

To explain the structure of the data - the Horse table contains ALL horses in the universe of horses whereas HorsesCurrentlyatDruids is just those that are at the Druids Venue - hence the start and end date fields in that table.

So your SQL will populate the pull down with a full list of horses but not the subset. It is the subset issue that I am having a problem with.

Thank you for your help so far.

I'm just using MS Access lookups at the moment to test the tables structure design and check the system will work before rebuilding it using front end forms and reports etc. If there is an easier way to do what I am trying to do, I am open to that. Thank you again.

[Micron]

If there is an easier way to do what I am trying to do, I am open to that.

IMO, the easier way is to design with the end game in mind, not to use features that you apparently don't intend to use. That approach basically means forget what you had working because now you're going to do it the right way. OOPS. Now trying to do it the right way I'm having design issues. Does that make sense to you?

My approach is tables first, then queries that will be used behind forms/reports, then forms/reports. If I build a form that won't allow me to enter data because something about the table schema or query is wrong, the form is of no use. If I can edit/append/delete in the query, there's every chance I will be able to do so in the form as well.

[CJ_London]

I’m not at at my pc for a few days but your subset is defined in the Druid’s table so as stated before you don’t need it in the activity table which if I recall is unique to the Druid table anyway. Makes me question whether the Druid’s table is correct unless it is the only ‘currently at’ location you will ever use

[RogerMcElroy]

IMO, the easier way is to design with the end game in mind, not to use features that you apparently don't intend to use. That approach basically means forget what you had working because now you're going to do it the right way. OOPS. Now trying to do it the right way I'm having design issues. Does that make sense to you?

My approach is tables first, then queries that will be used behind forms/reports, then forms/reports. If I build a form that won't allow me to enter data because something about the table schema or query is wrong, the form is of no use. If I can edit/append/delete in the query, there's every chance I will be able to do so in the form as well.

So the table structure I have is correct in that there is a total universe of horses and then a table which joins on horse ID but stored the horses which are present at one location with the start and end dates - so that is normalised to store the data only in one location and to have the least duplication. I believe that is correct and needs no change.

I will need a query though which pulls out the horse name (text field) from the horses table, based on horse ID in the horsescurrentlyatdruids table to use in a pull down list. At the moment, it is a table pull down list but that matters not - even if I am not intending to use that in the long term, I still need the query in the input forms later so my question I guess is regarding the sql required to pull back that subset from the horsescurrentlyatdruids table but to include the name of the horse which is only held in the horses table.

thanks once again.

[Micron]

One of us isn't grasping the whole thing. Maybe that's me, but if you want to query a field in a table that is a lookup field, then I don't see how a query that works on that type of field will be of any use after you do away with that field structure. In a table lookup field, what is stored is a number that points to a hidden table that contains the actual records, so what you see is the value, but it is being retrieved from that hidden table. If you do away with that structure and normalize the tables yourself, I don't see that query working any more. That's why I cannot see doing it one way and saying I'm going to fix/alter it later but for now I need a query that will work after I fix it. Perhaps that's not what you're saying so I should leave this to others who are in sync with you and your approach. I don't use table level lookups because of the issues they present, so I will not be able to help much here.

[RogerMcElroy]

One of us isn't grasping the whole thing. Maybe that's me, but if you want to query a field in a table that is a lookup field, then I don't see how a query that works on that type of field will be of any use after you do away with that field structure. In a table lookup field, what is stored is a number that points to a hidden table that contains the actual records, so what you see is the value, but it is being retrieved from that hidden table. If you do away with that structure and normalize the tables yourself, I don't see that query working any more. That's why I cannot see doing it one way and saying I'm going to fix/alter it later but for now I need a query that will work after I fix it. Perhaps that's not what you're saying so I should leave this to others who are in sync with you and your approach. I don't use table level lookups because of the issues they present, so I will not be able to help much here.

Is it easier if we just forget that the SQL I am looking for is in the table lookup section and just imagine it is in a separate query - all I really need is the SQL statement to pull out the horse name from the horse table, based on the horse ID value in the horsescurrentlyatdruids table?

[Micron]

I gave you all I know about that in my first post. If that's not it, then sorry - unless you want to provide a db for us to play with.

[Gicu]

I think you just need to use what Ajax suggested in post #2 and just add the horsescurrentlyatdruids table and join the Horse.ID to horsescurrentlyatdruids.HorseID just like you have it in the relationships. But that will only allow you to add new records for the horses already in horsescurrentlyatdruids (all others will not show up because you limit with the join). So I think Ajax was right in post#2 as it is customary for a lookup/combo box to show all available records.

Cheers,

[RogerMcElroy]

I gave you all I know about that in my first post. If that's not it, then sorry - unless you want to provide a db for us to play with.

Happy to upload the database but the limit seems to be quite small for this - the database is 2.65mb - any ideas. The limit seems to be 500k. Do you know if it is possible to do another way - perhaps with a link to a google drive share?

The issue is that the subset of horses from the horse table, which is shown in the horsescurrentlyatdruids table does not include the horse name and I cannot seem to create a pull down list in forms, queries or tables which contains the horsename field relating to the horseID field for a record in the horsescurrentlyatdruids table. It's completely my lack of knowledge that is the issue so I do appreciate any assistance.

[Bob Fitz]

Compact and Repair db then Zip it.

[RogerMcElroy]

So this SQL returns the pull down list I would like to use to select the horseID in the ActivityTeamPersonHorse table or a query based on that table

SELECT Horse.HorseName
FROM Horse INNER JOIN HorsesCurrentlyatDruids ON Horse.ID=HorsesCurrentlyatDruids.HorseID;

But when I put that in the lookup value for the table it brings back a blank list, I guess because the table value needs to convert the name into a corresponding ID value.

[RogerMcElroy]

DLPC (1).zip

OK - thanks - I think that should be attached as a zipped file.