Join Table on Expression ? (1 Viewer)

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

 

[Pat Hartman]

I'm with June on this one.
Mushing data is generally wrong. It is better to keep the separate parts as separate fields. If you need to make a unique index on your "separate fields", use the indexes dialog. You cannot use the table Design view to create multi-column indexes.

Then leave your PK as an autonumber and use that in all your joins.

 

[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

 

[Pat Hartman]

thanks June, I meant CANNOT