Moving data in a table

[soslippy]

hi

i have a table which contains fields: Vessel, Type, Volume, Alcohol and i want to move things around from vessel to vessel, combining the contents sometimes or emptying them all together. please advise

 

[soslippy]

so far i have

SELECT a.tank, a.type, b.tank, b.type
FROM table1 AS a, table1 AS b
WHERE a.tank = [from tank]
AND b.tank = [to tank]

 

[AlexMidd]

Right, so this query selects data from table1 under two guises (a and b) where the tank field matches [from tank] or [to tank] (input paramaters?). So, what do you want to do next? Have fun! )

Alex Middleton

 

[soslippy]

Well you might have guessed table1 contains my vessels and their contents and I want to move stuff from one vessel to another. The query I mentioned displays the vessels from where I want to move stuff to and from, but I can’t update that query. So I want a query which will ask which vessel I am coming from and which vessel I am going to and how much I am going to move and which will then update the table accordingly.

 

[AlexMidd]

As a database designer, guessing is not in my business. Guessing is why some organisations end up with computer systems costing millions that are totally useless. It just seemed to me that you have two tables in your query with the same fields. If you want to have vessels with "stuff" attached you need two tables, one for the vessels and anything that has a 1-to-1 relationship with one, and another for the stuff which presumably is on a 1-to-Many relationship (i.e. each vessel can have several items of "stuff&quot. You then need to have a foreign key field in the stuff table that stores the primary key of the vessels table, thus showing which vessel the item belongs to. (Forgive me if this is how you already have it but it was not readily obvious from your posts).

You then have to have some way, as you say, of allowing the user to select a "from" vessel and a "to" vessel. In effect, all the user needs to do is select an item, then the "from" vessel is know to the system, then select a vessel to pass it to. All this needs to do is change the vessel id for that item to the id of the "to" vessel.

There are many ways this can be done but my instinct would be to have a form which can be opened showing one vessel with any items that belong to it in a list box or a subform. The user can select one of these items then select a "to" vessel, e.g. from a combo box filled with valid vessel id's (you could have a 2 column drop-down with the bound column being the vessel id and the other being the name of the vessel, so that users know which vessel each id refers to).

Then write code, e.g. in the combo box after update event, to change the vessel id for the item to the new code. Hopefully this is something along the lines you are thinking of, but if not, give us a shout. Have fun! )

Alex Middleton