Using column and row variable in a table to get one data output on form

[ar45]

Hi,

I'm trying to create a database using access and am very new to the software so would appreciate some help!
The purpose of the database is to display a thickness, which is dependent on a size and pressure. I have this data stored in tables. The thickness is also different for material type, standard and temperature, so there are multiple different tables per material depending on the temperature and standard.

I currently have the database set up with a main form, where the user can input the material, standard and temperature using combo boxes. I have a button where I have written some simple VBA code, which will take the user to a second form (different for different inputs). On this second form, I would like the user to be able to input the size and pressure required, and have the thickness displayed in a text box at the top of the page, with the use of a search button. However, I have really been struggling to get this to work!

I have been partially successful when using a sub form of the table embedded into the form. From this, I was able to use the combo box wizard so the user is able to select the size and it will display the correct row data i.e. it displays all the thicknesses for all pressures for a certain size. However, I want to add a second combo box and the user can select the pressure, from this a single thickness value from the table displayed.

I've been scouring the internet but I couldn't find advice for something similar, I have tried using crosstab queries but couldn't get this to work. I also tried writing code and looking at the relationships in the query builder but haven't had any luck.

I think it is made more complicated because in the table, all the pressures are extra columns and there doesn't seem a way I can group them together as all being 'pressure' or run a search looking at all of these. I also have different pressure ranges in different tables, so couldn't do a single search for all the pressures.

Does anyone have any idea how to get this to work or if it is possible? I'd be really grateful for any help, if you could run through it step by step that would be great as I'm quite new to this! Ideally I would prefer to write code but any ideas to get it to work would be amazing!

Many thanks in advance

 

[theDBguy]

Hi. Welcome to AWF!

Designing a database starts with creating a sound table structure. After that, everything else should follow with less struggles. I think what you're asking is very possible, but I was just curious about your table structure. Are you able to post a screenshot of your table relationship window?

 

[The_Doc_Man]

Concur with theDBguy. You ALWAYS start with design of your data layout and work outward from there. If you are not familiar with the term "normalization" then I respectfully suggest your starting point is a little bit of study on that subject. If you search this forum for issues in normalization, just use the Search button near the right side of the menu bar. If you search the web, you must use "Database Normalization" because many other disciplines use the term "normalization" - including chemistry, statistical math, politics, diplomacy,...

If you search the web, start reading articles from .EDU sites first. Not that the .COM sites are bad, but often they have something to sell which would be more likely to distract you.

A well-normalized database would be the best starting point to assure that you can get the best out of your project.

 

[ar45]

Thanks both for your replies!!

Unfortunately, I can't share the document as it is work related, but I currently don't have any relationships between the tables!

Thanks for the info about normalisation, I will have a look into it, unfortunately I haven't had loads of time to familiarise myself with the software before I started this project.

I was wondering what would be a good way to start regarding normalisation? I was thinking I could make a new table for the pressure and somehow link it to the other tables, but not sure how as it is not a single column, it is multiple. Is the idea with normalisation to break up the table into smaller chunks?

Thanks again

 

[arnelgp]

is this for pressure vessel/tanks?

 

[ar45]

is this for pressure vessel/tanks?

Yes!

 

[Minty]

I think you could show us some of the base tables without giving away any trade secrets.

We would only need to see 2 or 3 lines of a couple of the standards tables.