Recordset not updatable due to Multiple Tables in Query

[dragon232]

Okay guys, so here's the problem I'm currently struggling with. I've identified using a smaller test query that the reason the recordset isn't updatable is that my query is using two tables, which are not joined or linked at all (nor can I join them). Here's why it's using two tables. The first and main table is a Salaries Table - each record in there represents a different employee. The second table is a Tax Table - there is only 1 record in there, with multiple fields, each field containing a different tax percentage. In the query I have the fields of the Salaries Table, and salaries calculations for each employee using the taxes from the Tax Table. Say one column in the query says:



Net_Income: [Hours_Worked]*[Taxes].[Income_Tax]
^ It doesn't actually say that, but I'm simplifying for illustration purposes. [Hours_Worked] would be a field from the Salaries Table, and [Taxes].[Income_Tax] a field from the Taxes Table. The reason why I have the Taxes in a table is because my boss wants to modify the tax percentages when they change (so there is a form built on that table to allow this), and he doesn't want to go around each formula in the query to modify them.

So I'm not sure what the best solution to this problem is. Should I make fields in the query like:

Income_Tax: 16%

That would obviously solve the problem as the query would only use one table, but then the user would have to go into the query design to change the percentages. Is there any other better way to do it? Thanks!

[dragon232]

The only other way I am thinking about is to make a separate VBA function for each tax in part, and have the function something like:

Public Function Income_Tax()
Income_Tax = DLookup("[Income_Tax]", "Taxes", "[ID]=1")
End Function

And then whenever I need the tax, I call up the VBA function... There would be many functions though. Is there a better way to achieve this?

Or would it be better to have one single function like:

Public Function Taxes(Tax As Double)
Dim Income_Tax As Double = DLookup("[Income_Tax]", "Taxes", "[ID]=1")
...
If Tax = 1 Then
Taxes = Income_Tax
ElseIf Tax = 2 Then
Taxes = Health_Tax
...
And so on, which I can use to request any tax?

[ranman256]

Due to the nature of the multi tables, you may get a non updatable, so you need to find a minimum joins that DO allow an update.

[dragon232]

Well I can't join them can I? One table has many records, and the other 1 record, with no common fields at all.

[aytee111]

Change the query to a make-table creating a local table, use that to do your updating in a second query.

[dragon232]

Change the query to a make-table creating a local table, use that to do your updating in a second query.

Too complicated. My solution ended up being:

Public Function Taxes(Tax As String)

Taxes = DLookup(Tax, "Taxes", "[ID]=1")

End Function

And calling this function in the query whenever I needed access to the Taxes table.