Join Table on Expression ?

[GK in the UK]

Can I join a table on an expression ?

I have a field in a table called thAcYrPrd which is a long in the format 202006 where 06 = 6th period of year 2020. I decided to do it this way because it makes selection between a period range in the query straightforward. (Periods do not necessarily have any relationship to dates).

I have two redundant fields thAcYr and thAcPrd which store 2020 and 6 respectively and when I made the change to the longer numeric format I knew these fields would have to go.

Now I'm attempting to remove them, but thAcPrd (6) is JOINed to a table, PrdNames.

I can get the period portion (6) out of the longer format (202006) in the query but I find I can't join the PrdNames table. I went into the SQL statement and changed the join to the expression but Access says it can't support it.

So ... is there a way, or must I leave the period part as a duplicate field in the table ?

 

[theDBguy]

Hi. Just to clarify, are you trying to create a JOIN between two tables in a query or are you trying to create a relationship between two tables?

 

[Isaac]

You can create a query that outputs a new column using your expression, (making sure the datatype that the expression results to, is appropriate for the join), and then join to the query.

 

[isladogs]

Remove the relationship then delete the field

 

[June7]

Query can join on table Calculated field. But if you will have to extract data in order to join records, why not keep the separate fields? It is always easier to concatenate than parse. Year and period are discrete data elements and qualify for own fields.

 

[June7]

@Pat Hartman, did you mean "can use the table design" instead of "cannot"?

 

[GK in the UK]

Colin, I need the relationship. I need to go to to tblPrdName to find the text name of the numbered period.

Just to clarify, my query works and is already JOINed to tblPrdNames. But it's joined on my 'redundant' field thAcPrd (a numeric 6). But thAcPrd is a sub set of thAcYrPrd (202006) so it fails normalisation. It's no bid deal to me to leave the field in, but strictly speaking it's surplus.

I want to lose the table field thAcPrd. But I still want to fetch PrdName but I will have no field to join it on.

Pat, is this any different to a date format which is 3 items mushed into 1 ? I don't really have any use for the individual parts.

June7, you're saying I can join the table on the expression? how? Access said it couldn't support the join.

The reason I decided to save the data like this is largely because of the ease of querying the data. I have 4 dropdowns for query criteria:

Start year (eg 2020), start period (anything from 1 to 53 if our periods are weeks, 1 to 4 if our periods are quarters, 1 to 12 if our periods are months)
End year (eg 2021), end period

Then I 'mush' those fields up and select 'between 202001 AND 202053'. Doesn't work as a date field. But, I want to show a descriptive name for the period and I have to go to tblPrdNames.

Here's the query with the Expression field that I can't join on ([thAcYrPrd] Mod 100 AS AcPrd,)

When I change
ON tblPrdNames.PrdNameID = tblTransHeaders.thAcPrd (the field I want to lose)
to
ON tblPrdNames.PrdNameID = AcPrd (the expression)
Access says Join not supported.

PrdNameID is AutoNumber.

SELECT tblTransHeaders.TransHeaderID, tblPrdNames.PrdName, tblTransHeaders.thCustSuppFk, tblTransHeaders.thDocType, tblTransHeaders.thDocReference, tblTransHeaders.thYourRef, tblTransHeaders.thDate, tblTransHeaders.thDueDate, tblTransHeaders.thAcYrPrd, [thAcYrPrd] Mod 100 AS AcPrd, Sum(Nz([tlNetValue],0)+Nz([tlVatValue],0)) AS GrossValue
FROM (tblPrdNames RIGHT JOIN tblTransHeaders ON tblPrdNames.PrdNameID = tblTransHeaders.thAcPrd) LEFT JOIN tblTransLines ON tblTransHeaders.TransHeaderID = tblTransLines.tlTransHeaderFK
WHERE (((tblTransLines.tlLineType)=0))
GROUP BY tblTransHeaders.TransHeaderID, tblPrdNames.PrdName, tblTransHeaders.thCustSuppFk, tblTransHeaders.thDocType, tblTransHeaders.thDocReference, tblTransHeaders.thYourRef, tblTransHeaders.thDate, tblTransHeaders.thDueDate, tblTransHeaders.thAcYrPrd;

 

[Isaac]

Have you tried creating an underlying query using the expression, then joining to that query? #3

 

[GK in the UK]

I pondered over #3 (thank you) but I thought I had that covered, the MOD function presumably returns a numeric, the field I want to join is AutoNumber. Is there another way I could try it?

 

[GK in the UK]

Alternatively, if I should keep the fields separate, and lose the 'combined' field what would the function be to select the range ? I'll admit I thought that 'between' 2 longs in the query was likely to give the best performance. I only have to combine them once but the query would have to concat them for every transaction. Am I over-thinking it ?

 

[Isaac]

I pondered over #3 (thank you) but I thought I had that covered, the MOD function presumably returns a numeric, the field I want to join is AutoNumber. Is there another way I could try it?

Mod returns Integer. You should be able to join an Integer with an AutoNumber (long integer) column. At least in a test I just performed, I was able to join Integer with Long Integer. I was also able to join Integer with AutoNumber. But you can give it a try and see, I was just wondering if it had been tried.

edit - I stand corrected, Mod returns a datatype depending on the arguments passed. You could try it with your data and find out, depends on what is passed in.

 

[theDBguy]

Alternatively, if I should keep the fields separate, and lose the 'combined' field what would the function be to select the range ? I'll admit I thought that 'between' 2 longs in the query was likely to give the best performance. I only have to combine them once but the query would have to concat them for every transaction. Am I over-thinking it ?

Initially, I thought you were talking about a Calculated Column. If not, maybe you give it a try too.

 

[GK in the UK]

? vartype(202006 mod 100)
3

3 is long so matches the joined field but Access isn't happy with it.

 

[isladogs]

I've only skim read this, so forgive me if I'm missing the point here.
If you delete the redundant field, your query could use filter criteria for the period instead of a join.
It would be a bit slower but as I assume you won't have a huge number of records, I doubt the difference would be noticeable

 

[CJ_London]

you can use a non standard join - it won't work in the query builder but you could have a separate query which contains whatever you want to know about a period.

your non standard join might look something like

SELECT *
FROM tblPrdNames INNER JOIN tblTransHeaders ON tblPrdNames.PrdNameID = tblTransHeaders. thAcYrPrd mod 100

edit - corrected join

 

[June7]

Query joining on table Calculated field works. Cannot use it in Relationship Builder.

As @pisorsisaac@gmail.co stated in post 3, if you do calc in query then have to use that query in another query to join to that calculated field.

 

[GK in the UK]

Oops I said PrdNamesID is AutoNumber, it is long but it's a number from 1 to 53 and as the PK it's indexed of course.

I have got it working in the existing query along the lines suggested by pisorisaac and CJ_London like this:

(SELECT PrdName FROM tblPrdNames WHERE tblPrdNames.PrdNameID = tblTransHeaders.thAcYrPrd mod 100 ) AS PeriodName

So now I can get rid of my surplus fields.

Thanks to all contributors,

 

[Isaac]

Glad you got it sorted out, good luck

 

[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.