Input data into a select query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,
I have a problem with inputing data into a query made up of several tables.
To be specific: I have a main form "Orders"(one order just for one supplier only and deliver to one destination), and a sub form "Orders Details". I am creating the query for the sub form. This is pretty much similar with the sample database "Nothtwinds", but the underlying tables are a bit more tricky. the underlying tables include:
1, table Orders Details: OrderID, ComponentID, quantity
2, table Components: ComponentName, ComponentID
3, table Component Details: componentID, SupplierID, UnitCost

I want the query to display and be able to input:
1, ComponentName
2, Quantity
3, UnitCost
4, ExtendedCost

and finally, i want to create a subform based on the query for the main form, to input and store the Order's details

How should i do this?


Any help will be highly appreciated!

Goodman
 
In order to be able to enter data into the select query, you need to
ensure that the primary key of the table containing the field that will
receive new data is in the query.

For instance, to make the field 'Component Name' updatable, the field
'ComponentID' needs to be included in the select query.

I have sometimes found that if a query is acting like it is
non-updatable, I can work around it by changing the data-set type from
'Dynaset' to 'Inconsistent Updates'. Try the primary key thing first--I
know that you won't be able to update anything without it. Change to
Inconsistent Updates as a second-to-last resort.

I don't really want to go into advising you on setting up the
parent-child form scenario that you described because the schema that
you laid out isn't that good.

I recommend, first of all, combine the 'Components' and 'Component
Details' tables. They belong together as they have the same primary key
(Unless you can receive particular components from more than one
Supplier--In that case, I can see why you broke it out this way).
 
Thanks, Douglas. Unforturnately, we do have this occassion that one component can be supplied by more than one suppliers---this is the real trouble to me. actually the table Component's primary key is ComponentID only, but Component Details has 2 as primary key: ComponentID and SupplierID
Do you have any advice to me to cope with it

Many thanks

Goodman
 
Back
Top