query - express builder

[chrisho]

I have two tables A & B, I need to check from B tables column 11 to find out
if there is anything like the table A column 8. In column 11, it contains
something like the following Key - xkjklckxkxS1357635lkjwkjwwkw and there are
a bout 5000 rows in Tables b which contains the above key in which would have
different Sxxxx number and in Table able column 8, it contains something like
S1357635, S1357636 to S 1358900 etc...which would be about 6000 rows.

Can someone shed the light to show me how I can write the builder to match
both tables....

I have written something like this but it does not work.

checking: IIf([final] Like [Telstra Query]!SERV_NUM,"yes","not matching"),
am I missing something?

 

[John W. Vinson]

I have two tables A & B, I need to check from B tables column 11 to find out
if there is anything like the table A column 8. In column 11, it contains
something like the following Key - xkjklckxkxS1357635lkjwkjwwkw and there are
a bout 5000 rows in Tables b which contains the above key in which would have
different Sxxxx number and in Table able column 8, it contains something like
S1357635, S1357636 to S 1358900 etc...which would be about 6000 rows.

Can someone shed the light to show me how I can write the builder to match
both tables....

I have written something like this but it does not work.

checking: IIf([final] Like [Telstra Query]!SERV_NUM,"yes","not matching"),
am I missing something?

Yes.

The LIKE operator accepts wildcard characters in the query criterion: *
matches any string of characters, ? matches any single character, # matches
any numeric digit, [AZ] matches any letter from A to Z.

If you don't USE a wildcard character it works just like the = operator -
exact match (it'll be slower).

I think you want a query criterion on Column 11 of

LIKE "*" & B.[Column 8] & "*"

so it will search Column 11 for any value starting with an arbitrary string of
characters, followed by the value from Column 8, followed by any other string
of characters.

 

[chrisho]

John,

tks for your prompt reply, I have entered like below but once I try
executing the script, it still treats the as an input parameter...I suspect I
have written incorrectly.

check: IIf([final] Like "* " & [Telstra Query]![SERV_NUM] & "*",[Telstra
Query]![SERV_NUM],"not matching")

chris

I have two tables A & B, I need to check from B tables column 11 to find out
if there is anything like the table A column 8. In column 11, it contains
something like the following Key - xkjklckxkxS1357635lkjwkjwwkw and there are
a bout 5000 rows in Tables b which contains the above key in which would have
different Sxxxx number and in Table able column 8, it contains something like
S1357635, S1357636 to S 1358900 etc...which would be about 6000 rows.

Can someone shed the light to show me how I can write the builder to match
both tables....

I have written something like this but it does not work.

checking: IIf([final] Like [Telstra Query]!SERV_NUM,"yes","not matching"),
am I missing something?

Yes.

The LIKE operator accepts wildcard characters in the query criterion: *
matches any string of characters, ? matches any single character, # matches
any numeric digit, [AZ] matches any letter from A to Z.

If you don't USE a wildcard character it works just like the = operator -
exact match (it'll be slower).

I think you want a query criterion on Column 11 of

LIKE "*" & B.[Column 8] & "*"

so it will search Column 11 for any value starting with an arbitrary string of
characters, followed by the value from Column 8, followed by any other string
of characters.

 

[John W. Vinson]

John,

tks for your prompt reply, I have entered like below but once I try
executing the script, it still treats the as an input parameter...I suspect I
have written incorrectly.

check: IIf([final] Like "* " & [Telstra Query]![SERV_NUM] & "*",[Telstra
Query]![SERV_NUM],"not matching")

Please explain. "it still treats the as an input parameter" - treats the WHAT
as an input parameter?

Perhaps you could post the complete SQL for your query, indicating how (if at
all) the two queries are being joined.

 

[chrisho]

Hi, here is my SQL but i have not join these two tables together as there is
not definite column in both tables are in common. However, I know that in
Table A, there will have a service number column which could be used to match
with table B column 11. Do I still get these two tables joined?


elstra.ID, Telstra.ACC_NUM, Telstra.BILL_NUM, Telstra.INVCG_COSVC_DESC,
Telstra.BILG_TRANS_DESC, Telstra.SERV_NUM, Telstra.SUMM_AMT,
Telstra.SUM_AMT_CR, Telstra.SERV_LOCN1, Telstra.SERV_LOCN2,
Telstra.SERV_LOCN3, Telstra.SERV_LOCN4, Telstra.SO_ITEM_QTY,
Telstra.BIT_STA_DT, Telstra.BIT_END_DT, Telstra.ALPHA_GIRN,
Telstra.PROD_BILG_ID, Telstra.BILG_ELMT_CD, Telstra.SE_PROD_LOCN_FID,
Telstra.SE_PROD_DESC_FID, Telstra.UNIT_RATE_VAL, Telstra.PUR_ORD_NBR,
Telstra.TRAN_TYPE_DESC, Telstra.SERV_NBR_LBL, Telstra.TXT_GNRC_DSCNT_AMT,
Telstra.TXT_GNRC_NET_AMT, Telstra.TXT_GNRC_NET_AMT_CR,
Telstra.TXT_GNRC_GST_AMT, Telstra.TXT_GNRC_GST_AMT_CR,
Telstra.TXT_GNRC_PRC_AMT, Telstra.TXT_GNRC_PRC_AMT_CR,
Telstra.TXT_UNIT_RATE_PRC, Telstra.PVC_EXTN, Telstra.A_END_SERV_NBR,
Telstra.B_END_SERV_NBR, Telstra.A_END_DLCI, Telstra.A_END_CIR,
Telstra.B_END_DLCI_VPI, Telstra.B_END_CIR_VCI, Telstra.IVN_NUM
FROM Telstra;

John,

tks for your prompt reply, I have entered like below but once I try
executing the script, it still treats the as an input parameter...I suspect I
have written incorrectly.

check: IIf([final] Like "* " & [Telstra Query]![SERV_NUM] & "*",[Telstra
Query]![SERV_NUM],"not matching")

Please explain. "it still treats the as an input parameter" - treats the WHAT
as an input parameter?

Perhaps you could post the complete SQL for your query, indicating how (if at
all) the two queries are being joined.

 

[John W. Vinson]

Hi, here is my SQL but i have not join these two tables together as there is
not definite column in both tables are in common. However, I know that in
Table A, there will have a service number column which could be used to match
with table B column 11. Do I still get these two tables joined?

I'm confused.

What is Table A? What is Table B? What is Telstra? What is the fieldname of
column 8 (Access doesn't use column numbers)? What is the fieldname of Column
11? If you don't have a join then YOU DON'T HAVE A JOIN.

elstra.ID, Telstra.ACC_NUM, Telstra.BILL_NUM, Telstra.INVCG_COSVC_DESC,
Telstra.BILG_TRANS_DESC, Telstra.SERV_NUM, Telstra.SUMM_AMT,
Telstra.SUM_AMT_CR, Telstra.SERV_LOCN1, Telstra.SERV_LOCN2,
Telstra.SERV_LOCN3, Telstra.SERV_LOCN4, Telstra.SO_ITEM_QTY,
Telstra.BIT_STA_DT, Telstra.BIT_END_DT, Telstra.ALPHA_GIRN,
Telstra.PROD_BILG_ID, Telstra.BILG_ELMT_CD, Telstra.SE_PROD_LOCN_FID,
Telstra.SE_PROD_DESC_FID, Telstra.UNIT_RATE_VAL, Telstra.PUR_ORD_NBR,
Telstra.TRAN_TYPE_DESC, Telstra.SERV_NBR_LBL, Telstra.TXT_GNRC_DSCNT_AMT,
Telstra.TXT_GNRC_NET_AMT, Telstra.TXT_GNRC_NET_AMT_CR,
Telstra.TXT_GNRC_GST_AMT, Telstra.TXT_GNRC_GST_AMT_CR,
Telstra.TXT_GNRC_PRC_AMT, Telstra.TXT_GNRC_PRC_AMT_CR,
Telstra.TXT_UNIT_RATE_PRC, Telstra.PVC_EXTN, Telstra.A_END_SERV_NBR,
Telstra.B_END_SERV_NBR, Telstra.A_END_DLCI, Telstra.A_END_CIR,
Telstra.B_END_DLCI_VPI, Telstra.B_END_CIR_VCI, Telstra.IVN_NUM
FROM Telstra;

This references only one table, not TableA and TableB, and it starts in the
middle.

I'd be glad to help, but I do need to know what it is you're working with!

 

[chrisho]

My apology for any confusions.

Table A is from the data provided from our supplier(fields like account_no.,
bill_no., service_no., Unit_rate, charge amt...etc) this data is the
breakdown of a bill and it includes 5000 services Oct 09 charges identified
by service_no.

Table B is from our inhouse data records with the field like
(carriers_name(more than one carriers), Terminated or not, unit_rate,
contract_no., special keys(The supplier' service_no. is
recorded in this field. it looks like these(i.e. XXXXS655323XXX, or
XXS644320XX or sometimes XS632224XXXXX...etc). Therefore, my thoughts are to
match the supplier charges to our inhouse data records by using like
functions...as below.

The reason I try to match the table B with Table A is to locate the service
is still alive or terminated once I can match by the service number.

pse advise what I can do.

tks

 

[John W. Vinson]

My apology for any confusions.

Table A is from the data provided from our supplier(fields like account_no.,
bill_no., service_no., Unit_rate, charge amt...etc) this data is the
breakdown of a bill and it includes 5000 services Oct 09 charges identified
by service_no.

Table B is from our inhouse data records with the field like
(carriers_name(more than one carriers), Terminated or not, unit_rate,
contract_no., special keys(The supplier' service_no. is
recorded in this field. it looks like these(i.e. XXXXS655323XXX, or
XXS644320XX or sometimes XS632224XXXXX...etc). Therefore, my thoughts are to
match the supplier charges to our inhouse data records by using like
functions...as below.

The reason I try to match the table B with Table A is to locate the service
is still alive or terminated once I can match by the service number.

You've explained the BUSINESS situation, which helps somewhat.

You have not posted the names of your tables, or much of anything that would
help me parse the SQL you posted. The SQL you posted does not have any
reference to Table A or Table B - the only table it references is Telstra,
which doesn't help me much!!!!

That said... try creating a query with the two tables (whatever they're named,
I still don't know). Initially join service_no to [special keys]. This will
only find exact matches.

Then open the query in SQL view (see, if you'ld posted your table names and
some readable SQL I could have given you the actual SQL!) and find the clause

FROM [Table A] INNER JOIN [Table B] ON [Table B].[special keys] = [Table
A].[service_no]

Edit this to read

FROM [Table A] INNER JOIN [Table B] ON [Table B].[special keys] LIKE "*" &
[Table A].[service_no] & "*"

This "non equi join" will find all records where the "Special keys" field
contains the service_no embedded within it.

Do note that storing multiple facts (multiple "special keys") concatenated in
a mishmosh of values in one [special keys] field is dreadfully bad design and
has already come back to bite you... and may bite you worse later. Fields
should be "atomic", having only one value; if you have a one (account) to many
(carriers, special keys) relationship, you really should model it as a one to
many relationship *to another table*, with one record per carrier or per
special key!

 

[chrisho]

Hi, it seems yours SQL is working now. However, after I run the scripts, I
have found a small number matched / like special keys which shown more than
once. Why is that?

My apology for any confusions.

Table A is from the data provided from our supplier(fields like account_no.,
bill_no., service_no., Unit_rate, charge amt...etc) this data is the
breakdown of a bill and it includes 5000 services Oct 09 charges identified
by service_no.

Table B is from our inhouse data records with the field like
(carriers_name(more than one carriers), Terminated or not, unit_rate,
contract_no., special keys(The supplier' service_no. is
recorded in this field. it looks like these(i.e. XXXXS655323XXX, or
XXS644320XX or sometimes XS632224XXXXX...etc). Therefore, my thoughts are to
match the supplier charges to our inhouse data records by using like
functions...as below.

The reason I try to match the table B with Table A is to locate the service
is still alive or terminated once I can match by the service number.

You've explained the BUSINESS situation, which helps somewhat.

You have not posted the names of your tables, or much of anything that would
help me parse the SQL you posted. The SQL you posted does not have any
reference to Table A or Table B - the only table it references is Telstra,
which doesn't help me much!!!!

That said... try creating a query with the two tables (whatever they're named,
I still don't know). Initially join service_no to [special keys]. This will
only find exact matches.

Then open the query in SQL view (see, if you'ld posted your table names and
some readable SQL I could have given you the actual SQL!) and find the clause

FROM [Table A] INNER JOIN [Table B] ON [Table B].[special keys] = [Table
A].[service_no]

Edit this to read

FROM [Table A] INNER JOIN [Table B] ON [Table B].[special keys] LIKE "*" &
[Table A].[service_no] & "*"

This "non equi join" will find all records where the "Special keys" field
contains the service_no embedded within it.

Do note that storing multiple facts (multiple "special keys") concatenated in
a mishmosh of values in one [special keys] field is dreadfully bad design and
has already come back to bite you... and may bite you worse later. Fields
should be "atomic", having only one value; if you have a one (account) to many
(carriers, special keys) relationship, you really should model it as a one to
many relationship *to another table*, with one record per carrier or per
special key!

 

[John W. Vinson]

Hi, it seems yours SQL is working now. However, after I run the scripts, I
have found a small number matched / like special keys which shown more than
once. Why is that?

I have no idea, since I don't know what's in your table or what actual query
you used. Example?