Forum Discussion

Poida22's avatar
Poida22
Copper Contributor
May 26, 2022

Add data from unrelated table into form

Hi, im a bit stuck as im new to learning access.

I have a basic form based on a table.

My form is a sub assembly of a Part

in the form you can add a task and minutes.

 

I have another table that has Rates, Labour markups etc. but not related to the Sub assembly.

I am trying to get the sub assembly form to include the Rates so it will calculate the labour costs.

But i dont want to for every new sub assembly i create have to select the labour rates, i just want it to look up the current rate from the rate table.

 

I hope i made sense and someone might be kind enough to help out 🙂

 

Cheers Poida

  • arnel_gp's avatar
    arnel_gp
    Steel Contributor
    how do you Distinguish the Current rates?
    do you enter the Latest date for each rate or you use Autonumber field, the maximum autonumber is the latest rate?

    create first a Query (qryLatestRate) that will return the latest Rate from rate table.

    using Datefield:

    select Rate from yourTable where DateField = (select top 1 max(dateField) from yourTable);

    using Autonumber (say, ID)

    select Rate from yourTable where DateField = (select top 1 max(id) from yourTable);


    then you can use Dlookup():

    =Dlookup("Rate","qryLatestRate")

Resources