Listview pulling data from a second table

[Yesideez]

I've got a form "properties" and at the bottom I've got a listview gadget that I'd like to pull data from a "worklogs" table where the property ID matches a field in the worklogs table.



I've written the query (below) but I can't seem to find out how to make it so as I'm browsing through the properties the listview automatically shows any work logs if any are present.

If this is a simple thing a pointer in the right direction would be appreciated otherwise a link to a good tutorial would be appreciated.

SELECT [worklog].[id],[worklog].[address],[worklog].[postcode],[worklog].[dateassigned],[worklog].[workcompleted],[worklog].[paymentreceived],[worklog].[added] FROM worklog WHERE [properties].[id]=[worklog].[_propertyid] ORDER BY [address];

[Rod]

You lack a join in your SQL to the properties table. I have attempted to rewrite your SQL:

SELECT id, address, postcode, dateassigned, workcompleted, paymentreceived, added FROM worklog INNER JOIN properties ON worklog.[_propertyid] = properties.id ORDER BY address;

I know that you are told to put names inside brackets and to explicitly address every column by prefixing the table name but in your case omitting these items makes the SQL more readable. Prefixing a name with an underscore is allowed but not a good idea; I have left the brackets around it as I'm not sure what the SQL parser would do with it otherwise.

What exactly do you mean by a 'listview gadget?'

PS The SQL tags won't work for me so the sample SQL remains in plain text.

[Yesideez]

Thanks for that - I've been using MySQL for around 10 years and didn't think I'd be needing to join with another table, still trying to get my head around how Access queries run in this environment.

A listview gadget is one of these: http://www.c-sharpcorner.com/UploadF...stViewImg2.gif

Why is underscore not a good idea?

I use those so that foreign keys stand out, have done since I started using MySQL, never had a problem with them.

The query I posted was a direct copy & paste job from Access.

[Rod]

Hi,

Most important: does my SQL work?

Alright ...

MySQL is a RDBMS (Relational Database Management System). Access is also a RDBMS plus a lot more. (Actually JET is the RDBMS that Access uses.) SQL (Structured Query Language) is an industry wide standard database query language and is not exclusive to Access. Access follows the industry standard fairly closely and deviations from standard are well documented. Joining tables is not peculiar to Access.

I knew there is a standard where foreign keys (or is it primary keys?) are prefixed by an underscore - so it's MySQL, is it? The only instance I know of (to my cost!) where underscores cause a problem in Access is in the naming of user events. This I discovered in v2003 and I have seen no documentation to say that the 'bug' has been corrected. Otherwise my objection to underscores is more cosmetic. Access will often introduce underscores in the names it constructs. For this reason I avoid underscores - all special characters in fact - and stick to capitalisation for breaking-up my names. I find the resulant code more readable and easier to interpret.

Er ... the PS was a comment for the moderators. Do they ever read these posts? I placed SQL tags around my text and the result was an empty exhibit!

[Yesideez]

thanks for explaining that, I'll consider replacing the underscore with something else like FK or fk.

The query did and didn't work. It does pull out the records but then it also pulled out a lot more as well.

If I add a WHERE clause in:

worklog.[_propertyid]=properties.id

That gives me exactly the same.

I tested with a record in the properties table I know has had some work done on it:

worklog.[_propertyid]=6397

That showed me the records I need - just need to figure out how to use the id from the property being viewed at the time.

[Rod]

Yup, a WHERE clause is what you need. Where is the value of the 'id being viewed?' I assume it's in some control or other. Put the explicit reference to this control in the SQL and all should be well.

[Yesideez]

How would I do that?

The "id" field is the primary key of the properties table (and all others)

[Rod]

OK.

SELECT id, address, postcode, dateassigned, workcompleted, paymentreceived, added FROM worklog INNER JOIN properties ON worklog.[_propertyid] = properties.id WHERE worklog.[_propertyid] = xxxxx ORDER BY address;

The question is what to substitute for xxxxx.

Hopefully somewhere on your form, visible or hidden, enabled or disabled is the 'id' value. Suppose that it is in a control named, txtId, and that it is numeric, then substitute xxxxx with Me.txtId. (I won't give the text/string value syntax which is slightly different.) If 'id' is not on the form then refer to the underlying recordset, [_propertyid] or id is probably a column in the recordset.

Some considerations:

• Me is shorthand for the object in which the code is running - in this case the form. It avoids all that tedious Forms!.. nonsense.
• Me.txtId should properly be written as Me.txtId.Value but as Value is the default property then it is not necessary to be explicit.

[khalid]

What is the [properties].[id]? Is it numeric or its text?
Never face problem with _ underscore in my table/field names, also [ ] is good idea around the tables/fields names if there is spaces between the field/table names.
Calling [properties].[id] (table.fieldname) is necessary if the same field name exists in two tables. Otherwise ambiguous error would accord.
Naming the reserved word (properties, field, name etc) using as a field name would be painful and creates problem in VBA.