Tail function

[canberry]

Hey all. Can i use the tail function in a join statement. If so How?
I am having this issue cas the pin# in Student table is 0234 while in yearReport it is 234. So if it possible to compare the tail in the code below would help me alot.

Select Student.studentID, yearReport.* INTO studentAll
From yearReport Left Join student ON yearReport.pin = student.pin1;

 

[pbaldy]

Not familiar with a tail function, but you can do things like:

Left Join student ON yearReport.pin = Clng(student.pin1)

or maybe

Left Join student ON Format(yearReport.pin, "0000") = student.pin1

but either way you will lose the ability to view the query in the design grid.

 

[canberry]

thanks for the reply, i recieved a type mismatch error. The pin numbers are set to text in the database

 

[pbaldy]

Can you post a sample db?

 

[MSAccessRookie]

Not familiar with a tail function, but you can do things like:

Left Join student ON yearReport.pin = Clng(student.pin1)

or maybe

Left Join student ON Format(yearReport.pin, "0000") = student.pin1

but either way you will lose the ability to view the query in the design grid.

I believe the Op is referring to a Function that works like the UNIX Tail Function, which retrieves the last line of a file, value of a dataset, etc. I do not know of a similar Access Built-In Function, although I am sure that one could probably be built (if one has not been built already).

 

[canberry]

I tried attaching the file but i am getting a DB error from the forum. The file is within the size limit.
Should the Clng function be only used on Int datatype or it works fine with text ?

 

[pbaldy]

The CLng should work on text, as long as there are only numbers within the text.

 

[Banana]

Did you zip it?

CLng can accept any datatypes and returns a Long. It's possible that while you convert one to Long, the other side is still a text and thus the error.

When I saw the title Tail function, I immediately thought you would want to get the last row, which the answer would be to do this:

SELECT TOP 10 * FROM MyTable ORDER BY foo DESC;

 

[pbaldy]

Or maybe even TOP 1.

 

[Banana]

Could have had made it clear that the TOP can be specified to be whatever by the user (and a percent as well, if that's what OP's after). I just put down 10 because that's the tail's default- last ten lines of a file.

It will also teach me to make my subjects and verbs agree! Oh well.

 

[canberry]

The problem with the TOP idea is that the pin varies with characters.

I attached the DB.

The Query name is cifLink

SELECT DISTINCT AnguillaFdms.*, ECIslandsCif.MERCHCIF INTO Everyhitng
FROM AnguillaFdms LEFT JOIN ECIslandsCif ON AnguillaFdms.MerchantNumber = ECIslandsCif.MERNBR;
So ECIslands would be Student and AnguillaFdms would be Student in the original post

 

[datAdrenaline]

You can do somthing like this ...

SELECT vtblStudents.studentID, vtblYearReport.* INTO studentAll
FROM (SELECT *, 0.0 + pin As vPin
       FROM yearReport
       WHERE pin Is Not Null) As vtblYearReport
    LEFT JOIN
       (SELECT *, 0.0 + pin1 As vPin
        FROM student) As vtblStudents
    ON vtblYearReport.vPin = vtblStudents.vPin;

Then you will be able to use the Query Designer if you so choose. Just for an FYI, when I the prefix "vtbl" is read "virtual table <blah>" since the name is aliased and the prefix "v" from vPin1 is read "virtual Pin1" since the expression is aliased .. that is just my crazy naming convention, please don't feel obligated to follow it . Also, the coersion of the text Pin and Pin1 is accomplished by the expression, to learn how that works, click here

 

[pbaldy]

I'm not clear on the objective. That query runs without error. I tested the join with the Format() function, which also ran without error. What are you trying to get that you aren't getting?

 

[canberry]

i tried it with the clng function and it didnt run. Could you post your version

 

[pbaldy]

Did you try Brent's method? He's better with SQL than I am, so his method is probably more efficient.

 

[datAdrenaline]

WOW ... Thanks Paul, I am humbled by your kind words! ...

-----

canberry ...

Give this a shot, and see if this will work for you ...

SELECT DISTINCT vAnguilla.*, vECIslands.MERCHCIF
FROM 
   (SELECT *, 0 + MerchantNumber As vMerchantNumber FROM AnguillaFdms) As vAnguilla
   LEFT JOIN
   (SELECT *, 0 + MERNBR As vMerchantNumber FROM ECIslandsCif) As vECIslands
   ON vAnguilla.vMerchantNumber = vECIslands.vMerchantNumber

 

[canberry]

thanks datAdrenaline. It ran fine with that DB. I tried it on another with pretty much the same data. But mismatch error came again. COuld you tell me if i applied u approach correctly.

Original Code----------
SELECT fidelityReport.*, ecislandscif.MERCHCIF INTO fidelityAll
FROM fidelityReport LEFT JOIN ecislandscif ON fidelityReport.MerchantID = ecislandscif.MERNBR;

New Approach Applied------------
SELECT DISTINCT vfidelity.*, vECIslands.MERCHCIF
FROM
(SELECT *, 0 + MerchantID As vMerchantNumber FROM fidelityReport) As vfidelity
LEFT JOIN
(SELECT *, 0 + MERNBR As vMerchantNumber FROM ECIslandsCif) As vECIslands
ON vfidelity.vMerchantNumber = vECIslands.vMerchantNumber;