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;
Take a look at this thread. The entry at the end has a tutorial on how to import into Access from Excel and combine the tables.

http://www.excelforum.com/excel-programming/772480-merging-multiple-xls-files.html

Alan

 

[kenny9002]

Dear Alan

Thanks for your kind suggestion. I am sorry that I am not good with Access at all - just a novice. I was wondering if you can help me further with this.

Can I send to you sample data so you can help me. I believe that it will help me a great deal. I am sorry that I have to bother you this much. Your further help with this will be greatly appreciated. If it is OK to send you the sample data by email, please let me know and I will do just that.

Again, thanks.

Kenny

 

[alansidman]

Kenny;
In order to allow all who may have a solution, I suggest that you post your information with dummy data and slimmed down so it is not to big to a site like box.net as a shared file. Then post the URL for those who wish to look at it and can then make a suggestion. It is best to keep this information in the public forum and not take it private to maximize the benefit.

Alan

 

[kenny9002]

Thanks Alan for your prompt response. I appreciate your kindness.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I have prepared two slimmed down databases - one for Product Purchases and the other for Sales to Customers. I used Excel to do that. However, I am not sure how to Post those. You advised that I should post the URL. I am sorry, these are all new to me.

If you kindly advise me re the steps to take, I will act on it accordingly. Rather than have the two databases on two different Excel sheets as I would have them in real life, I have, for ease of space management, put together the two databases on one Excel sheet - the Purchase Database above and the Sales below. As soon as I get further directive from you, I shall Post them for your further help.

Thanks.

Kenny

 

[alansidman]

Go to www.box.net Sign up. Follow their instructions for sharing a file and upload the file. They will give you the URL (address) for the file which you can then post in this forum. You cannot post attachments in this forum

Alan

 

[kenny9002]

Hi Alan,

I have acted as you advised. The information has been posted with dummy data. The two spreadsheets namely, Purchases Database and Sales Database, respectively (all in one workbook) named Stock & Sales DB.xls has been posted through box.net, and the URL is http://www.box.net/shared/n6n0khqz7u.

I hope that I have done it correctly, and that you would be able to access it and provide me the help that I need.

As indicated earlier, I wish to be able to combine the two databases and be able to analyze Sales by Date, Customer, Product, etc. and also be able to analyze Purchases by Date, Product, etc. and also be able to determine and analyze unsold stock - if possible - by Product.

Thanks for all your help.

Kenny

 

[alansidman]

Kenny;
I have looked at your spreadsheets and imported them into a db and then joined them. You should be able to do the same. I urge you to look at a tutorial on how to create queries as that will be how you will get all your data extracted. Once you have read these through--give them a try and post back with where you are having issues. Use your dummy tables so that we can help you if you run into difficulties.

You might want to start with these sites.

http://www.databasedev.co.uk/queries.html

Here is some info on importing tables that you may want to review also.

http://www.databasedev.co.uk/import-excel-into-ms-access.html

Alan

 

[kenny9002]

Thanks Alan for your Post. I have looked at the links you recommended and I'm willing to put in some effort to enable me to learn. Given that I have not had any prior experience - starting from the scratch, I know I'd have to crawl through until I get there.

However, given the urgent need for this part of the exercise in setting up the recording and analysis of our purchases and sales, I will need further help from you. I am very sorry for being a pain. I promise I will give some time to learn as I go on.

To enable me to complete this part sooner, please could you kindly upload for me the file containing the Access db you have done and a query that combines them and creates a pivot table exported in excel, to enable me to see what it looks like. It will also help me to see what you have done and understand it.

The family purchases and sales outfit is a small one - not big and therefore does not really involve very many data. What I intend to do is when I get your file, I'll replace the dummy data with the real data (as I go through it and learn) and, I believe that it will come out right for me. With that for a start, my Pivot Table output will be just what I require. That will also help me as I strive to learn the ropes.

Thanks again for your kindness. I appreciate you.

Kenny

 

[alansidman]

Kenny;
Here is the link
http://www.box.net/shared/8j3nhbre7k

I uploaded the two spreadsheets. Joined them in several different queries on the product (common field). You will see how I created different queries using different fields by looking at the design view of the queries. Not all fields are used in all the queries. I hope this will be helpful for you.

Alan