Help Anybody?

[snoopy85]

Hi all,

My database consists of tables named "purchases_0108" to "purchases_0708". For the 4 digits of the tables' name, the first 2 digits stands for the month and the last 2 digits stands for the year.

The tables consist of fields :

1) "name" (Company name that I bought the item from)
2) "date" (Date I bought the item)
3) "rate" (Currency rate when I bought the item)
4) "invoice" (Invoice number of the item)
5) "article" (Article number of the item)
6) "description" (Description of the item)
7) "size" (Size of the item)
8) "material" (Material of the item)
9) "finishing" (Finishing of the item)
10) "quantity" (Quantity of the item)
11) "price" (Price of the item)

I also have the same set of tables named "sales_0108" to "sales_0708". Both "purchases" and "sales" tables have the same fields.

What I wish to achieve is have a code which can output my stock balance. There is similar items purchased at different dates in "purchases" tables and also same items sold at different dates in "sales" table. Which means that I have to add up the quantity for each item in each month of the "purchases" table, and take that to minus the quantity for the same item, if I have sold them, in the "sales" table.

I only know basic php programming like mysql_query and mysql_fetch_array, using the while ($row = mysql_fetch_array($result)) to output the records.

Any help from you guys is greatly appreciated. Thanks in advance.

[khess]

Well, first, don't use different tables. Use one table. Insert a field named Purchase_date. It will be much easier to do what you need then.

[snoopy85]

Hi khess,

Yup I got people asking me the same questions. Why would I create different months of each table and not just have 1 table for the purchases and 1 table for the sales. It would be much easier to output the balance.

What I was thinking at first was I wouldn't want too many records in a single table and having different months' table for each purchases and sales table would make it much neaty.

And at the end of each year I would create a table for the year end balance of each item.

I apologize if my structure of database is not good at all. Kindly give your guidance and advice. Thank you!

[R0bb0b]

What I was thinking at first was I wouldn't want too many records in a single table and having different months' table for each purchases and sales table would make it much neaty.

Guess that just depends on your personal definition of neat. But take it from someone who creates reports for a living, I think you're walking into a nightmare, but not one that can't be adjusted at a later date though.