Bringing Fields Together

[Sarah1991]

Hello,



I'm really hoping someone can help - I've spent the last hour or so trying to figure out how to do something on Access and I'm sure a similar question has already been asked but to be honest I'm not even sure what it is I need to look for - I've read about relationships and queries etc and I still can't figure it out :/

Anyway what is it that I'm trying to do is....

I have 3 tables - each of which represent a supplier and the stock that they currently have available.

Each of these suppliers sells different products by the same brand, what I want to be able to do is create a query (I think?) which I can save? so that I have a quick way of being able to see which products by are currently available to us from a particular brand.

So I guess it's a way of linking the "brand" fields together - then being able to filter that so that I can see just the one particular brand. If all the suppliers were listed on one table it would be simple because I could then just filter it to show the brand than I want.

I'm sure it's a really straightforward answer but I just can't get my head around the jargon.

I really hope someone can help many thanks

Sarah

[aytee111]

Yes, you definitely need to learn about relationships and more before diving in. Without a solid table design structure you will struggle and struggle for ever! What you have described here is an Excel file with separate worksheets. That is the opposite of how Access works.

There will be a table containing all suppliers; a table containing all brands which will be linked to a table containing all products. Another table will be a cross-reference containing the supplier ID and the product ID showing which supplier has which product.

Pick up a pen and paper and write out what I have just written. Understand it before going any further.

Here is a good starting point (post # 7): https://www.accessforums.net/showthread.php?t=65906

[Bulzie]

Also to help aytee111 and others with solutions, can you give examples of your current table and fields and sample data, what it looks like in the tables and what you want the output(query) to look like given the sample data.

[alansidman]

Here is what I believe is one of the best papers on data base design and normalization.

http://r937.com/relational.html

Once you normalize your data, your ability to extract data will become much easier.