Unupdatable Recordset

[j.t.w]

Here's the table structure...

dbo_IMITMIDX_SQL
item_no
item_desc_1
item_desc_2

dbo_IMINVLOC_SQL
item_no
avg_cost

On a subform, I'm wanting to display a recordset to allow users to
update the avg_cost field. This could easily be done if I just
displayed the item_no and avg_cost from the dbo_IMINVLOC_SQL table.
But, I'd also like to display the item_desc fields as well to make it
more intuitive when users update the avg_cost field.

Here's the query I'm working with...

SELECT dbo_IMITMIDX_SQL.item_no, Trim([item_desc_1]) & " " &
[item_desc_2] AS [desc], dbo_IMINVLOC_SQL.avg_cost
FROM dbo_IMITMIDX_SQL INNER JOIN dbo_IMINVLOC_SQL ON
dbo_IMITMIDX_SQL.item_no = dbo_IMINVLOC_SQL.item_no;

Does anyone have any suggestions on how I can accomplish this? Also,
please let me know if I should be posting this message in a different
group.

Thanks,
j.t.w

 

[Scott McDaniel]

Here's the table structure...

dbo_IMITMIDX_SQL
item_no
item_desc_1
item_desc_2

dbo_IMINVLOC_SQL
item_no
avg_cost

On a subform, I'm wanting to display a recordset to allow users to
update the avg_cost field. This could easily be done if I just
displayed the item_no and avg_cost from the dbo_IMINVLOC_SQL table.
But, I'd also like to display the item_desc fields as well to make it
more intuitive when users update the avg_cost field.

Here's the query I'm working with...

SELECT dbo_IMITMIDX_SQL.item_no, Trim([item_desc_1]) & " " &
[item_desc_2] AS [desc], dbo_IMINVLOC_SQL.avg_cost
FROM dbo_IMITMIDX_SQL INNER JOIN dbo_IMINVLOC_SQL ON
dbo_IMITMIDX_SQL.item_no = dbo_IMINVLOC_SQL.item_no;

Does anyone have any suggestions on how I can accomplish this? Also,
please let me know if I should be posting this message in a different
group.

Remove the calculated field (where you concantenate desc_1 and desc_2).

Further, why have 2 description fields if the obvious intent is to string them together?

Finally: if avg_cost described the Item, then this data might belong in the "main" table ... not sure about that, since
I don't know your data, but it would seem to me that a single item can have only one "average cost" ...

Thanks,
j.t.w

Scott McDaniel
scott@takemeout_infotrakker.com
www.infotrakker.com

 

[j.t.w]

Here's the table structure...


On a subform, I'm wanting to display a recordset to allow users to
update the avg_cost field. This could easily be done if I just
displayed the item_no and avg_cost from the dbo_IMINVLOC_SQL table.
But, I'd also like to display the item_desc fields as well to make it
more intuitive when users update the avg_cost field.

Here's the query I'm working with...

SELECT dbo_IMITMIDX_SQL.item_no, Trim([item_desc_1]) & " " &
[item_desc_2] AS [desc], dbo_IMINVLOC_SQL.avg_cost
FROM dbo_IMITMIDX_SQL INNER JOIN dbo_IMINVLOC_SQL ON
dbo_IMITMIDX_SQL.item_no = dbo_IMINVLOC_SQL.item_no;

Does anyone have any suggestions on how I can accomplish this? Also,
please let me know if I should be posting this message in a different
group.

Remove the calculated field (where you concantenate desc_1 and desc_2).

Further, why have 2 description fields if the obvious intent is to string them together?

Finally: if avg_cost described the Item, then this data might belong in the "main" table ... not sure about that, since
I don't know your data, but it would seem to me that a single item can have only one "average cost" ...

Thanks,
j.t.w

Scott McDaniel
scott@takemeout_infotrakker.comwww.infotrakker.com- Hide quoted text -

- Show quoted text -

Scott,

Thanks for your response.

I've tried taking away the item_desc fields completely but, still it
does not work. Once I link the two tables the recordset becomes
unupdatable. Is there a way around this? Or what other suggestions
could I look into?

FYI. I'm working with an ERP, which does not give me the flexability
to change the table or database structure. So, you're right, it would
seem that the avg_cost should be in the IMITMIDX_SQL table (which is
the master item table). That would go for the two description fields
as well.

Thanks again.
j.t.w