Form with multiple results for one section

[JesterGrafix]

Hello all. I am in the process of creating my first form in Access. I have created my query that has all the information that I need. My issue currently is, I have one field in my form that needs to have multiple returns in it. In my query, I have a list of items and the vendor and cost. There are multiple rows in the query for most of the parts, one for each vendor of that item. In my vendor box of the form, I want it to return each unique item, a vendor name and their cost for that part. Each of the other boxes will only need to show its information once, as this information is identical on each row for that part. When i created the form (just using the wizard for now until i figure out how to create one from scratch) it is only showing the first row in each of the sections. In my form, I will need the vendor name and then that vendors cost in a box beside their name. I have an old one that someone else made that is way more complicated than I can figure out which is why I am trying to create a new one that we can keep updated ourselves as the original person no longer works with us. Here is what the old one looks like, the arrow is what I am currently trying to replicate. Thanks for taking the time to look.





Chris

[kd2017]

Can you post your new database for review?

[JesterGrafix]

Can you post your new database for review?

It is telling me that my database is too big, it is 3 MB, I just clicked the attach button, is there another way that I can do it? If not I can link to it from my Google or OneDrive

[kd2017]

First try a compact and repair then zipping it and uploading the zip.

If it's still too big then try your cloud.

[JesterGrafix]

Okay, here it is, I got it to compress and then zipped it up.

[kd2017]

I'm not quite following how you have set up your tables. And I don't think you have it setup in a way currently where you can get a list of vendors for a material per the first screenshot.

For you to understand why that is do some studying on database normalization and relationships, you might also find that you come to understand the old database and there is no need to reinvent the wheel. What's wrong with the old database?

This link has a huge list of example database models: http://www.databaseanswers.org/data_...all_models.htm do a ctrl-f and look for the word inventory and see if any of the models apply to you.

If you're a visual learner you can check out this guy's youtube tutorials on learning ms access: https://www.youtube.com/user/Program...deEZ/playlists

[JesterGrafix]

The old database is just very old, and it has so many tables that it is very hard for us to update. The tables that I have are just Excel sheets that I imported into Access that have the information that I need. I am just trying to create something simple that they guys can use to research parts to see where it is located, who we get it from, and the other information that is in the form. It does not have to look like the original database at all, I just wanted something simple that all my guys can look at. Just so you can see the old one, here is a link to it. It is huge, even zipped up

https://drive.google.com/open?id=1Hh...Rm72vbUaDhui4N

[kd2017]

So I've looked over the old database. it's not terribly complicated, some things are not quite straight forward either but I don't know your business. Again, I think if you were able to find the time to study db concepts then you would better understand the old database.

Anyway, back to your original question. What you are seeing in the screen shot in the first post is a sub form that displays rows from a different table than your main form. The main form is based off a table that lists all the materials, the sub form is based off a separate table that lists vendors, the vendors table has a link to the material they produce.

In this first screenshot you can see how the last guy set up the relationships between tables. Notice the [Material Number] that I circled in red, that number used to link related rows of data together. You could say one material has many vendors, one material has many comments, and one material has many links.



In this screenshot I'm trying to illustrate what is linked to what. The red boxes are subforms that are placed on top of the main form. The orange lines point back to the actual form objects that are used. The red lines point back to the table that each subform is pulling its data from. On the right hand side is the Property Sheet for the Vendors subform, under the data tab you can see that its source object is [Vendors Subform] that I pointed to with the orange line, and that the link fields are "Material Number" that I pointed out in the other screenshot. The master field is a field from the data in the main form, the child field is a field from the data in the sub form. It's saying that what ever row of data you're on (material) in the main form, only display rows of data in the subform (vendors) that have matching data in the linked fields (Material Number).




You might find these video tutorials pretty relevant.
Tutorial on relationships: https://www.youtube.com/watch?v=NtRAyS0LLlk
Making subforms: https://www.youtube.com/watch?v=xPQdfZbqCQk


[EDIT]
On a side note, do any of your vendors provide many different materials?