Data Classification in Access

[osepkhanov]

Hi everyone,

I am just starting to use Microsoft Access and that is why I am here to ask for your valuable hints.

I have imported some data from excel and I need to do the following with it:

For example, I need to select a pump for a new project and I have a database with records from previous projects. I have several criteria based on which I want to make a selection: 1) Lets say I have 3 types of pumps I can select from: centrifugal, gear and screw. How can I make kind of drop down menu from where I can select the exact type of pump I need (for example, centrifugal); 2) Further to that I want Access to filter me data only from projects where centrifugal pumps were used; 3) Lets assume that next criterion is pump flowrate. I want to have some kind of operation ranges from where I can select (for example, 200-500 m3/h, 500-10000 m3/h and so on). So, if I select 200-500, Access filters data and shows me that only in 3 projects centrifugal pumps with 300 m3/h capacity were used; 4) Some further criteria could be also applied, but what I want to see in the end is Price for Corresponding Pump or 2 pumps, if both are appropriate.

Could you please help (e.g. provide some useful YouTube or other sources)? I hope this is the right place to post my query. If not, please direct. Many thanks in advance.

Cheers,
Orkhan

 

[Andrzejek]

Let's step back for a moment.
You have your data transeferd from Excel, that's fine.

How many tables do you have in Access?
What is the structure of your tables?
How are those tables related?

Have fun.

---- Andy

 

[osepkhanov]

Hello Andy,

Thank you for reply.

I have just made one simple table. Structure is quite common: in first row you can see main criteria based on which everything is classified (Project number, flowrate, diameter, length, price, and so on). In 2-... rows you have data from different projects. Hope this makes things clear.

--

Orkhan

 

[Andrzejek]

You can do your work based on what you have in your Access right now, but I would strongly encourage you to read Fundamentals of Relational Database Design. This will help you a great deal in your quest in Relational Database world.

Have fun.

---- Andy

 

[Andrzejek]

But, to answer your questions anyway with the data structure you have:
1)
If you have in a table:

[pre]
... ProjNo PumpType FlowRate ...
ABCD centrifugal 3.5
XYZ centrifugal 6.9
OOO centrifugal 7.9
UUU gear 1.1
NNN screw 8.8
WWW screw 7.3
[/PRE]
You can get: centrifugal, gear, and screw just once if you do:
[tt]
Select DISTINCT PumpType
From YourTableName
Order By PumpType
[/tt]

2)[tt]
Select * From YourTableName
Where PumpType = 'centrifugal'
Order by ...[/tt]

3)
You would need another table with those ranges.
FlowRate table[pre]
ID Min_Flow Max_Flow
1 200 500
2 500 1000
....
[/pre]


Have fun.

---- Andy

 

[osepkhanov]

Andy,

Thank you for reference. I am going through it at the moment.

What goes to your latest post, I did not quite get how to do that. Do you also use Access 2010? And isn't it more comfortable to make this kind of data filtering and categorization in Forms, rather than in Tables? I would like to use Access only as an interface for our sales engineers and use excel as a source file where I can work with my data (add, delete, modify).

--
Orkhan