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

[TABLE="width: 300"]
<tbody>[TR]
[TD]tblTest[/TD]
[TD][/TD]
[TD][/TD]
[TD="width: 71"][/TD]
[/TR]
[TR]
[TD]ID[/TD]
[TD]TestDate
[/TD]
[TD]Tester[/TD]
[TD]SampleNo[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1/01/18[/TD]
[TD]1[/TD]
[TD]4456[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1/05/18[/TD]
[TD]1[/TD]
[TD]5578[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1/08/18[/TD]
[TD]2[/TD]
[TD]8852[/TD]
[/TR]
</tbody>[/TABLE]

<tbody>
</tbody>

[TABLE="width: 200"]
<tbody>[TR]
[TD="width: 64"]tblComps[/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD]ID[/TD]
[TD]Comp[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]methane[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]ethane[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]argon[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 228"]
<tbody>[TR]
[TD="colspan: 2"]tblTestComponents[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ID[/TD]
[TD]TestID_FK[/TD]
[TD]Component[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1[/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]

Hope that provides some food for thought.