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

 

[kenny9002]

Hi Alan,

Thanks very very very much! I'll download it and work on it.

Best wishes.

Kenny

 

[alansidman]

Your Welcome. If you need assistance in designing more queries as I only did a few for demo purposes -- post back.

Alan

 

[kenny9002]

Hi Alan

Sorry, it was my fault! I did not inform you that I use Office 2003 and therefore Access 2003. The file you uploaded is in the Access 2007 format. I could not open it. Sorry about this.

I have the little program that converts Office 2007 Word and Excel documents to Office 2003. But unfortunately, this does not apply to the Access documents.

Kenny

 

[kenny9002]

Hi Alan,

i was wondering, please, if there is any other way of going round this other than me having to buy and install Office 2007.

Grateful for your advice.

Thanks.

kenny

 

[alansidman]

I will convert and repost. Here is the new file.
http://www.box.net/shared/2fi9ojsp80
Alan

 

[kenny9002]

Thanks Alan. I got the converted file and was able to open it. Very very grateful. I will work on it and if I have any proboems designing more queries, I shall ask for your help as you suggested.

You have been very wonderful. Grateful to you and to the forum.

Kenny

 

[kenny9002]

Hi Alan,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
I have had a very good look at the file and queries that you designed and have spent time trying to understand and follow through. It all looks very very exciting but, honestly, I'm lost - being such a novice with Access. I would need your assistance re the queries.<o></o>
<o></o>
After going through the queries, please I have a couple of observations. Meanwhile, as I said in my initial Post, in all these, what I really desire is to be able to - at any given time, run a report that will give me the following:<o></o>
a) Analysis of SALES by Date, Customer, Product, etc., and<o></o>
b) Analysis of PURCHASES by Date, Product and also be able to get a report that shows the unsold stock (of each category of product) - in terms of the number of unsold stock and the related cost.<o></o>
<o></o>
I am not expecting to desire any more than the above. I wish it was able to achieve this with the two databases as easily as I have stated them above. But I know that you and the other forum experts can.<o></o>
<o></o>
1. Re - the qHelper for On Hand query - From the way you have designed this query which gives the 'sum of qty purchased' and 'sum of qty sold', I believe that the query can be incorporated into the last of the queries you designed namely 'Sum of Purchases & Sales' (the name could be changed to Purchase Inventory instead of Sum of Purchases & Sales.<o></o>
<o></o>
2. Re - the qSum of Purchases and Sales query - The particular query has the following columns:<o></o>
a) Product Name, (b) Sum of Qty Purchased (c) Sum of Total Cost (d)Sum of qty sold, and then (e)Sum of Total Sales. In my opinion, what I propose is that - the (c) column should be renamed 'Total Cost of Qty Purchased'. The (e) column should be renamed 'Total Cost of Qty Sold' - and the data should be the total qty sold x the COST £ figure (not sales £ figure). I also think that for this query, there should be two additional columns, namely 'Stock At End' i.e. sum of unsold qty (column (b) less column (d). The final column being 'Cost of Stock At End' i.e. column (c) less column (d). I believe that this way, this query will provide the analysis we require in respect of unsold stock at any given time - in terms of qty and cost.<o></o>
<o></o>
3. Re -the qOn Hand Inventory - On clicking this, I get a message 'database engine cannot find the input table ... etc'. If the qSum of Purchases & Sales query is altered as suggested above, there may not be the need for this query. Your expert opinion is required here.<o></o>
<o></o>
The other two remaining queries - Sum of Customer, and Sum by Date are OK with me. The only other additional queries that I require for the complete report are -<o></o>
<o></o>
a) In respect of Sales: a 'Sum by Product' i.e. sales by product;<o></o>
b) In respect of Purchases:'Sum by Product' i.e. purchases by product, and 'Sum by Date' i.e. purchases by Date.<o></o>
<o></o>
Also, instead of using Pivot Table to generate the reports, is it possible to use the Reports feature to design the required reports. If it is involving, then I shall make do with using Pivot Tables. And finally, in using this, may I know if I have to go through the Pivot Table routine to obtain the reports/analysis anytime there is a desire for a report?<o></o>
<o></o>
Again, I am sorry for being such a bother. Thanks for your continued help.<o></o>
<o></o>
Kenny <o></o>
<o></o>

 

[alansidman]

Now that you have the data base, I can give you the SQL statements for the desired queries. Click on the object queries in the navigation. Click on New. Click on design. When the window opens with the listing for tables to include, click on close. In the upper left corner, it will now say SQL. Click on that and paste each of the SQL statements in. Save the query with a new name and open it.

Here is a SQL statement for Sales by date by customer by product

SELECT [SALES DATABASE].DateOfSale, [SALES DATABASE].CustomerLastName, [SALES DATABASE].CustomerFirstName, [SALES DATABASE].ProductName, Sum([SALES DATABASE].QtySold) AS SumOfQtySold, Sum([SALES DATABASE].TotalSales) AS SumOfTotalSales
FROM [SALES DATABASE]
GROUP BY [SALES DATABASE].DateOfSale, [SALES DATABASE].CustomerLastName, [SALES DATABASE].CustomerFirstName, [SALES DATABASE].ProductName;

Reports can be generated from Queries or reports. Suggest you try this using the wizard. Again, open the Report object and click on new and use the wizard. This is probably the easiest of things you can do. And only you can determine what you want them to look like.

qOn hand Helper was designed to work in the background to generate the On Hand query so that you know what the unsold inventory at any time is.

Item 2 was only created so that you could see what happens when you combine all the fields in one query based upon how you have your tables set up. I agree it is not functional. To change the name of any column, go to the query in design mode. Put your cursor in the column title box in front of the first character. Type the new name you want to call the field and follow it with a ":" . Access will change the name you see in the query results to the new name, ie. Total Cost of Qty Purchased: Sum of Total Cost.

Item 3. Did you delete the helper query or alter it? That is the driver for this query.

Item 3 continues. Here is the SQL for Sales by Product

SELECT [SALES DATABASE].ProductName, Sum([SALES DATABASE].QtySold) AS SumOfQtySold, Sum([SALES DATABASE].TotalSales) AS SumOfTotalSales
FROM [SALES DATABASE]
GROUP BY [SALES DATABASE].ProductName;

Purchases by Product

SELECT [PURCHASE DATABASE].ProductName, Sum([PURCHASE DATABASE].QtyPurchased) AS SumOfQtyPurchased, Sum([PURCHASE DATABASE].[Total Cost]) AS [SumOfTotal Cost]
FROM [PURCHASE DATABASE]
GROUP BY [PURCHASE DATABASE].ProductName;

Purchases by Date

SELECT [PURCHASE DATABASE].DateofPurchase, Sum([PURCHASE DATABASE].[Total Cost]) AS [SumOfTotal Cost]
FROM [PURCHASE DATABASE]
GROUP BY [PURCHASE DATABASE].DateofPurchase;

Suggest you look at this website for some video tutorials
http://www.datapigtechnologies.com/AccessMain.htm

At this point if you find this too overwhelming, you may want to hire a professional in your area to design and work on these for you. This forum is really designed to help you with programming and design issues and not to do your work for you. We are all willing to help with specific issues. It is now time for you to take the reins and drive this project to completion.

Alan

 

[kenny9002]

Dear Alan

Thanks heaps for all your help. You are a STAR! I owe you a huge gratitude. I am also very grateful to the administrators of mrexcel forum.

Thanks. I shall settle down to make use of all the knowledge and resources that you have given to me.

Kenny

 

[kenny9002]

Hi Alan,

Just a quick one: I have written an SQL for Sales by MONTH - to show the sales according to Month. The sales are entered on a date (daily basis) e.g. 2/3/2011, 15/3/2011, 18/4/2011, 23/4/2011 etc. I wish to do a query that will pull the dates together (including their resultant sales figures) and show them in months, e.g. the two in March, April, etc.

I append below a copy of my SQL:

SELECT [SALES DATABASE].DateOfSale, [SALES DATABASE].ProductName, Sum([SALES DATABASE].DateOfSale) As Month, Sum([SALES DATABASE].QtySold) AS TotalQtySold, Sum([SALES DATABASE].TotalSales) AS TotalSalesPrice<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
FROM [SALES DATABASE]
GROUP BY [SALES DATABASE].DateOfSale, [SALES DATABASE].ProductName;

Upon completion, I got the columns OK, the DateOfSale column correctly grouped the dates. But the 'Month' column came up with information that I did not understand, e.g. 40604, 81208, 40642, etc.

Please may I know where I went wrong in the SQL. If I get the 'Month' column right, I am prepared to discard the initial 'DateOfSales' column, leaving only the 'Month' column and the rest.

Thanks for your help.

Kenny