Manipulating Data in Access

[RockDarkwater]

I'm new to Access (moving over from Excel because the databases were too big) and I was wondering the best way to duplicate how excel manipulates data.

I have two tables that need to interact:

Table 1 - Gas Component Percentages in a sample (Methane, Ethane, etc.). Each sample processed is a record, each component is a column.
Table 2 - Gas Component constants (Molecular weight, Cubic Ft per Gallon, etc.). Each record is a component, each constant is a column.

How do I get a text box in a report or form to display the molecular weight of a sample? (Component Percent * Component Constant)

In excel, I'd write something like this:

=Hlookup("Methane", Table1!1:2, 2, False)*vlookup("Methane", Table2!A:B, 2, False)


Thanks in advance for the help!

 

[sheetspread]

Write a query that joins both tables and calculates the product (can also include distinctrow and transform/pivot). Access has dsum/dlookup functions but they're much slower.

 

[Micron]

each component is a column.

This is often the paradigm of Excel users. db data is supposed to be structured as row based, not columnar. Your situation seems to require a junction table. You probably ought to have something like tblComponents and tblTests with a linking table tblTestComponents (or similar names). If I used Test and Component as examples but made up my own components for simplicity, then something like

tblTest
ID	TestDate	Tester	SampleNo
1	1/01/18	1	4456
2	1/05/18	1	5578
3	1/08/18	2	8852

<tbody>
</tbody>

<tbody>
</tbody>

tblComps
ID	Comp
1	methane
2	ethane
3	argon

<tbody>
</tbody>

tblTestComponents
ID	TestID_FK	Component
1	1	1
2	1	3

<tbody>
</tbody>

Hope that provides some food for thought.