Join Table on Expression ? (1 Viewer)

Pat Hartman

Super Moderator
Staff member
Local time
Today, 17:58
Joined
Feb 19, 2002
Messages
42,976
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

Registered User.
Local time
Today, 21:58
Joined
Dec 20, 2017
Messages
274
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.
 
Last edited:

June7

AWF VIP
Local time
Today, 13:58
Joined
Mar 9, 2014
Messages
5,423
@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

Super Moderator
Staff member
Local time
Today, 17:58
Joined
Feb 19, 2002
Messages
42,976
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.
 

Users who are viewing this thread

Top Bottom