Join Table on Expression ?

[Pat Hartman]

Pat, is this any different to a date format which is 3 items mushed into 1 ?

Dates are not stored as three text pieces. They are stored as a double precision number representing the number of days from the origin point. The decimal portion is the fraction of time since midnight. So 3.5 is Dec 30, 1899 Midnight + 3.5 or Jan 2, 1900 Noon

You DO have use for the individual parts, You are CALCULATING them.

Mushing them to do the compares is not a problem. I was talking about storing them mushed.

 

[GK in the UK]

Indulge me a bit more as I find this an interesting conundrum.

If you feel this discussion is extending a bit please migrate it to Theory and Practice.

I still have duplicated data in my table so I'm not committed either way yet.
I'm storing my year and period, not as two text pieces but as a number with sufficient precision.
Year and period are easy to get with \ and MOD and appear on forms (which by their nature show only a tiny sub-set of transactions)
To select the transactions, I take user input for four values, and create two numbers, eg 201009 and 201205. Once, before my query runs.

My query compare is:
BETWEEN 201009 and 201205

Now let's say we have thAcYr and thAcPrd saved in the table as discrete parts and I no longer have the combined thAcYrPrd.

So the query compare is something like (untested it's giving me a headache, it could be flawed):
the OR is to pick up anything in 2011

WHERE
((thAcYr >= StartYear AND thAcPrd >= StartPrd) AND (thAcYr <= EndYear AND thAcPrd <= EndYear))
OR
(EndYear > StartYear+1 AND (thAcYr < EndYear)

I should really do that ? Is there a simpler way ?

edit: I think it is flawed needs more work.

 

[June7]

@Pat Hartman, I am confused by your comment about creating indexes. I CAN create multi-column index with table in design view.

Back to original question: "Can I join a table on an expression ?" Answer is a qualified Yes. I already pointed at that a table Calculated field can be used in a query JOIN. A query calculated field cannot be joined in DesignView.

I will say either approach for this data can be made to work. Since the data is simple with consistent structure, parsing would be easy. Concatenating discrete fields could be done in table or query and used in filter criteria. Yes, numbers should index faster than text. However, text that is only digits can be converted to numeric with functions.

 

[Pat Hartman]

I CAN create multi-column index with table in design view.

ONLY if you use the Indexes dialog was my point. And that dialog is not at all clear as to how this is done. Using just the table design, you can use Shift or Cntl - click to select a group all together or several fields that are not adjacent but this creates a primary key. This click method is not available to create a unique index.

Yes, you can join tables/queries on calculated fields. When I want to do that, I normally create queries to concatenate the fields and then a final query to join the query with the calculated fields but you can do it all in the same query. The point that others made is that doing this prevents Access from being able to use indexes to speed up the join process.

From tbl1 inner join tbl2 ON tbl1.fld1 & Format(tbl1.fld2, "00000") = tbl2.fld1 & Format(tbl2.fld2, "00000")

This assumes that fld2 is numeric. When you concatenate two numeric fields like this, you need to ensure that both fields are the same length.

The first field seems to be fixed at 4 digits but the second field could be different length. So, if you have 2020 and 10 and 2020 and 5, you want the concatenated values to be 202010 and 202005 respectively.