Combining two excel databases

[kenny9002]

Please I need help with combining two databases – Stock database and Sales database to enable me carry out analysis.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
In our small family business, I use Excel/VBA to make both product Purchase and Sales entries onto Excel Workbook. I use a Data Entry Form to record Purchases of products (We buy different types of products – but only from one supplier). These entries are recorded onto a Purchase Database on Worksheet A. An example of the Purchase database is something like this: Each of the following headings go onto a column in the Purchase Database:
<o> </o>
Purchase Database:

• Product
• Quantity purchased
• Date of Purchase
• Unit Cost<o> </o>

I also use a Data Entry Form to record Sales of those products to different customers. The Database for the sales is on Worksheet B, and the column headings are as follows:
<o> </o>
Sales Database:

• Name of Customer
• Contact No.
• Product sold
• Quantity
• Date of <?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-comffice:smarttags" /><st1:City w:st="on"><st1lace w:st="on">Sale</st1lace></st1:City>
• Unit Price
• Amount Paid<o> </o>

My problem and where I need help is this: At any given time, I would want to analyze Sales by Date, Customer, Product, etc. and also be able to analyze Purchases by Date, Product and be able to determine and analyze unsold stock – if possible, by product.
<o> </o>
I suppose this would involve combining, somehow, the two databases. I don’t have a clue how to achieve this and should be grateful for help, please.
<o> </o>
If, however, any of the experts thinks that the method that I am currently using will not help me to achieve my aim, I am open to any suggestion/help with any other method of recording the purchases and sales – with a view to achieving the desired analyses.
<o> </o>
Thanks for your kind help.
<o> </o>
Kenny

 

[number kruncher]

Obviosly the Difference between the Product(s) purchased and the Products(s) sold gives you your Stock no's, at any given time, which could be for the totals, or using dates could give you Stock no's totals at the end of each month, for instance.

Unit cost could then be used to give you closing stock figures.