Combining two 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

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

 

[alansidman]

Kenny;
Nice effort. You were very close. Look at this SQL and make sure to look at it in the design mode(commonly referred to as QBE)

SELECT (Month([DateOfSale])) AS [Month of Sale], [SALES DATABASE].ProductName, Sum([SALES DATABASE].QtySold) AS TotalQtySold, Sum([SALES DATABASE].TotalSales) AS TotalSalesPrice
FROM [SALES DATABASE]
GROUP BY (Month([DateOfSale])), [SALES DATABASE].ProductName;

You had a sum for the Month Dates versus a Group By and you needed to get only the month--not the exact date.
Alan