Move table information via button

[KLeabu46]

Hello,
I am working on a database for management of a retail store. I sell expensive sewing machines that each have unique serial numbers. I already have tables linked with one to many relationships and no data is duplicated. What I want to do is create a button that essentially moves a machine from my inventory table to my sold table (ie. Deletes the serial number from my inventory and saves it to my sold table) is there any way for me to do this?

Thanks,
Kleabu46

[MTP]

Sure, if you learn how to program macros that execute SQL code. It's a bit of a steep learning curve for the benefit, though.

Why not instead have a column in your inventory table named "Sold" that stores the sell date? Then to check your current inventory you run the query

Code: Select all

SELECT * FROM "Inventory" WHERE "Sold" IS NULL

And to see what you have sold

Code: Select all

SELECT * FROM "Inventory" WHERE "Sold" IS NOT NULL

[KLeabu46]

I was initially thinking of doing it this way because I am linking my customer table with the sold table, that way when I look up my customer I can see what machine I sold them and when. When I sell a machine to someone, I want to be able to locate a machine in my inventory, sell it to the customer (link it to them) and have it not show up when I go to sell a different one to someone else. Can you suggest a smooth way to do this?

[MTP]

You can have both "SoldDate" and "SoldTo" columns in the inventory table. To look up all machines sold to a particular customer by CustomerID:

Code: Select all

SELECT * FROM "Inventory" WHERE "SoldTo" = 5

or by CustomerName:

Code: Select all

SELECT * FROM "Inventory" 
   INNER JOIN "Customers" ON "Inventory"."SoldTo" = "Customers"."CustomerID"
   WHERE "Customers"."CustomerName" = "A"