How to combine two tables and/or records in Access 2010?

[Josephine87]

Hi all,

I’m really in need of some help from some Access experts! I’m a newbie to Access, but I got myself busy with quite a big project... I’m trying to map out the product supply to shops in the whole country. I’ve come quite far for a starter, but I keep having 3 questions, it would be great if you could help me with this!

I have, amongst other tables, two tables called Stock_counted and Reported_quantity.

The table Stock_counted has the following fields:
- Shop_number
- Product_code
- Quantity_counted

The table Reported_quantity has the following fields:
- Shop_number
- Product_code
- Quantity_in
- Quantity_lost_or_returned
- Quantity_out

1. What I would like to do is to make it one table based on Shop_number AND Product_code. So in one record I would like to have Shop_number, Product_code, Quantity_in, Quantity_lost_or_returned, Quantity_out and Quantity_counted. How do I do this, making sure that the quantities end up behind the right product and shop? It is possible that some shops don’t have all items, while other shops might not have all products reported, or both. On top of that, I have another table with a price for each product, which needs to be related to it as well.

2. It happens that some products are found more than once in one specific shop and therefore appear more than once in the table, with the same shop number and product code. How do I automatically SUM these quantities, so that this product only shows up once per shop?

3. Finally, I have some products which are the same but have slightly different descriptions and therefore different product codes. Is there a way to link them all under one (existing, one of the descriptions) code without having the other codes showing up anymore?

I would really appreciate it if anyone can help me with this, I’m desperate!!

Thank you so much,

Josephine
Access 2010 user

 

[jdraw]

You may wish to research this link
http://allenbrowne.com/appinventory.html

Getting your tables and relationships organized to support your requirements is the most important part of design.

Inventory control, you will find, is quite complex. If you Google for "Inventory Control" you'll find several forums and threads with a gamut of questions and answers.

There are some existing data models at www.databaseanswers.org that may be helpful in part.

Get your ERD set up and test your model with sample data and transactions.

You may find that defining/describing each of your entities in 2-3 lines will help you differentiate one product from another.

There is more info at this site that can help http://www.rogersaccesslibrary.com/forum/topic238.html

Good luck with your project.

 

[rmcclain64]

Hi all,

I’m really in need of some help from some Access experts! I’m a newbie to Access, but I got myself busy with quite a big project... I’m trying to map out the product supply to shops in the whole country. I’ve come quite far for a starter, but I keep having 3 questions, it would be great if you could help me with this!

I have, amongst other tables, two tables called Stock_counted and Reported_quantity.

The table Stock_counted has the following fields:
- Shop_number
- Product_code
- Quantity_counted

The table Reported_quantity has the following fields:
- Shop_number
- Product_code
- Quantity_in
- Quantity_lost_or_returned
- Quantity_out

1. What I would like to do is to make it one table based on Shop_number AND Product_code. So in one record I would like to have Shop_number, Product_code, Quantity_in, Quantity_lost_or_returned, Quantity_out and Quantity_counted. How do I do this, making sure that the quantities end up behind the right product and shop? It is possible that some shops don’t have all items, while other shops might not have all products reported, or both. On top of that, I have another table with a price for each product, which needs to be related to it as well.

2. It happens that some products are found more than once in one specific shop and therefore appear more than once in the table, with the same shop number and product code. How do I automatically SUM these quantities, so that this product only shows up once per shop?

3. Finally, I have some products which are the same but have slightly different descriptions and therefore different product codes. Is there a way to link them all under one (existing, one of the descriptions) code without having the other codes showing up anymore?

I would really appreciate it if anyone can help me with this, I’m desperate!!

Thank you so much,

Josephine
Access 2010 user

Josephine,

Did you ever find out how to do this? I'm trying to do the same thing and have not been able to figure it out.

Thanks
Bob