Windows 7 Forums

Welcome to Windows 7 Forums. Our forum is dedicated to helping you find support and solutions for any problems regarding your Windows 7 PC be it Dell, HP, Acer, Asus or a custom build. We also provide an extensive Windows 7 tutorial section that covers a wide range of tips and tricks.


Windows 7: How to combine fields in Access 2010 based on 2 equal fields?

23 Feb 2012   #1
Josephine87

Windows 7, 64 bit
 
 
How to combine fields in Access 2010 based on 2 equal fields?

Hi all,

I知 really in need of some help from some Access experts! I知 a newbie to Access, but I got myself busy with quite a big project... I知 trying to map out the product supply to shops in the whole country. I致e 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稚 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知 desperate!!

Thank you so much,

Josephine
Access 2010 user


My System SpecsSystem Spec
.
23 Feb 2012   #2
Kaktussoft

Microsoft Community Contributor Award Recipient

Microsoft Windows 7 Home Premium 64-bits 7601 Multiprocessor Free Service Pack 1
 
 

Quote   Quote: Originally Posted by Josephine87 View Post

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?
select shop, product, sum(quantityfield1), sum(quantityfield2)
from sometable
group by shop, product
My System SpecsSystem Spec
Reply

 How to combine fields in Access 2010 based on 2 equal fields?




Thread Tools Search this Thread
Search this Thread:

Advanced Search




Similar help and support threads
Thread Forum
Trouble typing in some fields
I'm not sure if this is a windows problem but sometimes when I try to fill in fields, it won't type what I want or types really slow or not at all. Also won't let me use caps or other symbols. Happens all the time on my banking website, my Yahoo account, etc. Any ideas?
Browsers & Mail
How Update User Name Fields
My system was initially set up with a misspelling for the User name and Computer name as Nichol or NICHOL-PC. Consequently, several fields in the HomeGroup Provider registry entry are wrong. RegEdit locks these field for update. Does anyone have a methodology/process for changing the names.
Hardware & Devices
Ms Fax Cannot Populate Fields in cover pages (.cov)
Win 7 Pro 64. Ms Fax does not permit updating of fields in cover pages. Info like senders name, recipient name, etc. cannot be entered. The controls/fields can be selected, but, entering or updating info does not work. I managed to copy a cover page created under Ms Fax under Xp and CAN...
Software
Colors of font and typing fields
Hello I've just installed my Win 7 yesterday for the first time and immediatly grabbed 'Dark Agility' Theme. Althought the theme looks very fine to me, i've found several problems such as black typing fields and black fonts on a dark grey background. Needless to say that it is really annoying...
Customization
MP3tag to spreadsheet - select fields
Hi there I'm fairly new to all this "Digital Music" stuff so excuse if this seems a trivial problem for a lot of users. I've ripped my CD's and now I'm trying to create some sort of decent library system. I'm using mp3tag v245a and it shows me the data I want to export to EXCEL (as a CSV...
Music, Pictures & Video


Our Sites

Site Links

About Us

Find Us

Windows 7 Forums is an independent web site and has not been authorized, sponsored, or otherwise approved by Microsoft Corporation. "Windows 7" and related materials are trademarks of Microsoft Corp.

ゥ Designer Media Ltd

All times are GMT -5. The time now is 09:56.

Twitter Facebook Google+



Windows 7 Forums

Seven Forums Android App Seven Forums IOS App