Help with String Field


S

slotmgr70

I have a table that records adjustments to player accounts. It lists the
date, time, amount and includes a "comment" field. The problem is, the
comment field includes some additional information that I don't need. Here is
an example of the string from a comment field:

"Player: (9618913) LINDA M SCHURR, Promo Adjusted -> 25.00, Beg. Bal ->
0.00, End Bal -> 25.00, NEW MEMBER"

The "NEW "MEMBER" portion is the comment, but the field includes all of the
other information about the adjustment transaction. I just want the comment
at the end of the string.

How can I grab just the comment info in a query formula? It's the last part
of the string after the last comma. Is there a formula that can grab the info
after the last comma?
 
Ad

Advertisements

C

Clifford Bass

Hi,

Try:

Right(Comment, Len(Comment)- InStr(1, Comment, ",") - 1)

Clifford Bass
 
S

slotmgr70

I tried it and got an ODBC error: "Invalid length parameter passed to the
RIGHT function (#356)"

Here is the formula I inserted using your layout:

Right([Comments],Len([Comments])-InStr(1,[Comments],",")-1)

I see what you are doing in the formula, but I must be messing it up
somehow. Should I be setting this formula as a criteria or as an expression?
 
C

Clifford Bass

Hi,

Sorry, forgot some issues :-( I was assuming that there was both a
comma and a space. Which may not always be true.

So adjust the formula to:

Trim(Right([Comments],Len([Comments])-InStr(1,[Comments],",")))

Also, there may be fields that do not contain any commas. With these
you get something like Len("xxx") - InStr(1, "xxx", ","), which becomes 3.
So it will return the all of the string. You will need to decide whether or
not you want to include them. There also may be some blank fields that are
Null. With those we end up with a Len(null) - InStr(1, Null, ","), which
returns Null. The Right() function does not care for Null.

So, in your query you will want to eliminate those that are null:

where .... Comments Is Not Null ....

If you want to eliminate anything without a comme use:

where .... InStr(1, Comments, ",") > 0 ....

Hope that helps,

Clifford Bass
 
F

fredg

I have a table that records adjustments to player accounts. It lists the
date, time, amount and includes a "comment" field. The problem is, the
comment field includes some additional information that I don't need. Here is
an example of the string from a comment field:

"Player: (9618913) LINDA M SCHURR, Promo Adjusted -> 25.00, Beg. Bal ->
0.00, End Bal -> 25.00, NEW MEMBER"

The "NEW "MEMBER" portion is the comment, but the field includes all of the
other information about the adjustment transaction. I just want the comment
at the end of the string.

How can I grab just the comment info in a query formula? It's the last part
of the string after the last comma. Is there a formula that can grab the info
after the last comma?

What version of Access?
If your version includes the InStrRev() function then you can use:

CommentData:Mid([FieldName],InStrRev([FieldName],",")+1)

Everything after the last comma will be returned.

Why don't you have all of the various bits of information in the
Comment field in separate fields in a table? By normalizing your data
you would make your life simpler.
 
C

Clifford Bass

Hi Fred,

Thanks for the reminder of the InStrRev() function. While I have used
it in the past, I forgot about that today.

Clifford Bass
 
Ad

Advertisements

S

slotmgr70

I tried the following formula:

Mid([Comments],InStrRev([Comments],",")+1)

And received the following error:
"Data type mismatch in criteria expression"

I also tried the other formula:
Trim(Right([Comments],Len([Comments])-InStr(1,[Comments],â€,â€)))

And I receievd an "ODBC--call failed" error

Any suggestions?

Clifford Bass said:
Hi Fred,

Thanks for the reminder of the InStrRev() function. While I have used
it in the past, I forgot about that today.

Clifford Bass

fredg said:
On Thu, 10 Sep 2009 13:59:03 -0700, slotmgr70 wrote:

What version of Access?
If your version includes the InStrRev() function then you can use:

CommentData:Mid([FieldName],InStrRev([FieldName],",")+1)

Everything after the last comma will be returned.

Why don't you have all of the various bits of information in the
Comment field in separate fields in a table? By normalizing your data
you would make your life simpler.
 
C

Clifford Bass

Hi,

What you will want is this, which will eliminate the nulls, which can
cause an error. It also looks for the last comma with InStrRev instead of
the first comma. It makes the assumption that there may or may not be one or
more spaces after the last comma and trims them off:

select Trim(Right([COMMENTS], Len([COMMENTS]) - InStrRev([COMMENTS], ",")))
as Actual_Comments
from tblYourTable
where [COMMENTS] is not null;

Hope that helps,

Clifford Bass
 
S

slotmgr70

Hi Clifford,

I'm still getting an error, but it's probably because I'm not very good with
SQL scripts. Here's the script I have so far. Can you take a look at it and
suggest a solution:

SELECT dbo_CDS_AUDITTRAIL.AuditGamingdate,
dbo_PBT_PROMO_ADJUSTMENT.Player_ID, [dbo_CDS_PLAYER.lastname] & ", " &
[dbo_CDS_PLAYER.firstname] AS [Player Name], [dbo_CDS_USER.lastname] & " ," &
[dbo_CDS_USER.firstname] AS [Adjusted by]
FROM ((((dbo_CDS_AUDITDETAIL INNER JOIN dbo_CDS_AUDITTRAIL ON
dbo_CDS_AUDITDETAIL.AuditTrail_ID = dbo_CDS_AUDITTRAIL.AuditTrail_ID) INNER
JOIN dbo_CDS_USER ON dbo_CDS_AUDITTRAIL.User_ID = dbo_CDS_USER.User_ID) INNER
JOIN dbo_CDS_AUDITOBJECT ON dbo_CDS_AUDITTRAIL.AuditTrail_ID =
dbo_CDS_AUDITOBJECT.AuditTrail_ID) INNER JOIN dbo_CDS_PLAYER ON
dbo_CDS_AUDITOBJECT.Meta_ID = dbo_CDS_PLAYER.Player_ID) INNER JOIN
dbo_PBT_PROMO_ADJUSTMENT ON dbo_CDS_PLAYER.Player_ID =
dbo_PBT_PROMO_ADJUSTMENT.Player_ID
WHERE
(((dbo_CDS_AUDITDETAIL.Comments)=Trim(Right([COMMENTS],Len([COMMENTS])-InStrRev([COMMENTS],",")))))
GROUP BY dbo_CDS_AUDITTRAIL.AuditGamingdate,
dbo_PBT_PROMO_ADJUSTMENT.Player_ID, [dbo_CDS_PLAYER.lastname] & ", " &
[dbo_CDS_PLAYER.firstname], [dbo_CDS_USER.lastname] & " ," &
[dbo_CDS_USER.firstname], dbo_CDS_AUDITTRAIL.AuditEvent_ID
HAVING (((dbo_CDS_AUDITTRAIL.AuditGamingdate) Between #9/3/2009# And
#9/4/2009#) AND ((dbo_CDS_AUDITTRAIL.AuditEvent_ID)=2630));


Clifford Bass said:
Hi,

What you will want is this, which will eliminate the nulls, which can
cause an error. It also looks for the last comma with InStrRev instead of
the first comma. It makes the assumption that there may or may not be one or
more spaces after the last comma and trims them off:

select Trim(Right([COMMENTS], Len([COMMENTS]) - InStrRev([COMMENTS], ",")))
as Actual_Comments
from tblYourTable
where [COMMENTS] is not null;

Hope that helps,

Clifford Bass

slotmgr70 said:
I tried the following formula:

Mid([Comments],InStrRev([Comments],",")+1)

And received the following error:
"Data type mismatch in criteria expression"

I also tried the other formula:
Trim(Right([Comments],Len([Comments])-InStr(1,[Comments],â€,â€)))

And I receievd an "ODBC--call failed" error

Any suggestions?

"Clifford Bass" wrote:
 
C

Clifford Bass

Hi,

The problem is that you are setting up a condition of "comments =
computed comments". So where there is a comments column that is null, the
computation, and therefore the query, bombs; for reasons previously stated.
You need to put the computation in the select portion of the query if you
actually want to see the results. Now, I see that you want to list rows
where there are comments and where there are not not any comments. So we
will have to deal with that in a slightly different method. This method will
ensure that Nulls get converted into zero-length strings ("") in order to
prevent the computation from bombing. You can do this by concatenating a
zero-length string to the column. So the computation will become:

Trim(Right([COMMENTS] & "", Len([COMMENTS] & "") - InStrRev([COMMENTS] & "",
",")))

And you will not use a condition on the comments column so that you get
all rows.

Also, for some reason you are doing a grouping where you are not using
any aggregate functions (Max(), Min(), Avg(), etc.). So change that to a
regular select query instead of a summary query. Try:

SELECT dbo_CDS_AUDITTRAIL.AuditGamingdate,
dbo_PBT_PROMO_ADJUSTMENT.Player_ID, [dbo_CDS_PLAYER.lastname] & ", " &
[dbo_CDS_PLAYER.firstname] AS [Player Name], [dbo_CDS_USER.lastname] & " ," &
[dbo_CDS_USER.firstname] AS [Adjusted by],
Trim(Right([COMMENTS],Len([COMMENTS])-InStrRev([COMMENTS],","))) AS
ACTUAL_COMMENTS
FROM ((((dbo_CDS_AUDITDETAIL INNER JOIN dbo_CDS_AUDITTRAIL ON
dbo_CDS_AUDITDETAIL.AuditTrail_ID = dbo_CDS_AUDITTRAIL.AuditTrail_ID) INNER
JOIN dbo_CDS_USER ON dbo_CDS_AUDITTRAIL.User_ID = dbo_CDS_USER.User_ID) INNER
JOIN dbo_CDS_AUDITOBJECT ON dbo_CDS_AUDITTRAIL.AuditTrail_ID =
dbo_CDS_AUDITOBJECT.AuditTrail_ID) INNER JOIN dbo_CDS_PLAYER ON
dbo_CDS_AUDITOBJECT.Meta_ID = dbo_CDS_PLAYER.Player_ID) INNER JOIN
dbo_PBT_PROMO_ADJUSTMENT ON dbo_CDS_PLAYER.Player_ID =
dbo_PBT_PROMO_ADJUSTMENT.Player_ID
WHERE dbo_CDS_AUDITTRAIL.AuditGamingdate Between #9/3/2009# And
#9/4/2009# AND dbo_CDS_AUDITTRAIL.AuditEvent_ID=2630;

Hopefully I got that all right. Note that sometimes when trying to get
something to work, it is easier to attempt it in a simple query until you
figure it out and understand what is happening. Then transfer it into your
more complicated query. So if I did not get it right, try doing a simple
select of the computed column from the table that contains the comments.

Good Luck,

Clifford Bass

slotmgr70 said:
Hi Clifford,

I'm still getting an error, but it's probably because I'm not very good with
SQL scripts. Here's the script I have so far. Can you take a look at it and
suggest a solution:

SELECT dbo_CDS_AUDITTRAIL.AuditGamingdate,
dbo_PBT_PROMO_ADJUSTMENT.Player_ID, [dbo_CDS_PLAYER.lastname] & ", " &
[dbo_CDS_PLAYER.firstname] AS [Player Name], [dbo_CDS_USER.lastname] & " ," &
[dbo_CDS_USER.firstname] AS [Adjusted by]
FROM ((((dbo_CDS_AUDITDETAIL INNER JOIN dbo_CDS_AUDITTRAIL ON
dbo_CDS_AUDITDETAIL.AuditTrail_ID = dbo_CDS_AUDITTRAIL.AuditTrail_ID) INNER
JOIN dbo_CDS_USER ON dbo_CDS_AUDITTRAIL.User_ID = dbo_CDS_USER.User_ID) INNER
JOIN dbo_CDS_AUDITOBJECT ON dbo_CDS_AUDITTRAIL.AuditTrail_ID =
dbo_CDS_AUDITOBJECT.AuditTrail_ID) INNER JOIN dbo_CDS_PLAYER ON
dbo_CDS_AUDITOBJECT.Meta_ID = dbo_CDS_PLAYER.Player_ID) INNER JOIN
dbo_PBT_PROMO_ADJUSTMENT ON dbo_CDS_PLAYER.Player_ID =
dbo_PBT_PROMO_ADJUSTMENT.Player_ID
WHERE
(((dbo_CDS_AUDITDETAIL.Comments)=Trim(Right([COMMENTS],Len([COMMENTS])-InStrRev([COMMENTS],",")))))
GROUP BY dbo_CDS_AUDITTRAIL.AuditGamingdate,
dbo_PBT_PROMO_ADJUSTMENT.Player_ID, [dbo_CDS_PLAYER.lastname] & ", " &
[dbo_CDS_PLAYER.firstname], [dbo_CDS_USER.lastname] & " ," &
[dbo_CDS_USER.firstname], dbo_CDS_AUDITTRAIL.AuditEvent_ID
HAVING (((dbo_CDS_AUDITTRAIL.AuditGamingdate) Between #9/3/2009# And
#9/4/2009#) AND ((dbo_CDS_AUDITTRAIL.AuditEvent_ID)=2630));
 
S

slotmgr70

Hi Clifford,

It worked! I have the comment for each record.

But now I have multiple identical records for some transactions. That's why
I was grouping them so I would only get one record for each transaction. When
I tried to change the Player ID, Player Name, Adjusted by, and amount to
"group by" i get the following error:

"you tried to execute a query that does not include the specified expression
"AuditGamingDate" as part of an aggregate function"

Here is the revised SQL statement again:

SELECT dbo_CDS_AUDITTRAIL.AuditGamingdate,
dbo_PBT_PROMO_ADJUSTMENT.Player_ID, [dbo_CDS_PLAYER.lastname] & ", " &
[dbo_CDS_PLAYER.firstname] AS [Player Name], [dbo_CDS_USER.lastname] & " ," &
[dbo_CDS_USER.firstname] AS [Adjusted by], dbo_PBT_PROMO_ADJUSTMENT.Amount,
Trim(Right([COMMENTS],Len([COMMENTS])-InStrRev([COMMENTS],","))) AS
ACTUAL_COMMENTS
FROM ((((dbo_CDS_AUDITDETAIL INNER JOIN dbo_CDS_AUDITTRAIL ON
dbo_CDS_AUDITDETAIL.AuditTrail_ID = dbo_CDS_AUDITTRAIL.AuditTrail_ID) INNER
JOIN dbo_CDS_USER ON dbo_CDS_AUDITTRAIL.User_ID = dbo_CDS_USER.User_ID) INNER
JOIN dbo_CDS_AUDITOBJECT ON dbo_CDS_AUDITTRAIL.AuditTrail_ID =
dbo_CDS_AUDITOBJECT.AuditTrail_ID) INNER JOIN dbo_CDS_PLAYER ON
dbo_CDS_AUDITOBJECT.Meta_ID = dbo_CDS_PLAYER.Player_ID) INNER JOIN
dbo_PBT_PROMO_ADJUSTMENT ON dbo_CDS_PLAYER.Player_ID =
dbo_PBT_PROMO_ADJUSTMENT.Player_ID
WHERE (((dbo_CDS_AUDITTRAIL.AuditGamingdate) Between #9/3/2009# And
#9/4/2009#) AND ((dbo_CDS_AUDITTRAIL.AuditEvent_ID)=2630));



Clifford Bass said:
Hi,

The problem is that you are setting up a condition of "comments =
computed comments". So where there is a comments column that is null, the
computation, and therefore the query, bombs; for reasons previously stated.
You need to put the computation in the select portion of the query if you
actually want to see the results. Now, I see that you want to list rows
where there are comments and where there are not not any comments. So we
will have to deal with that in a slightly different method. This method will
ensure that Nulls get converted into zero-length strings ("") in order to
prevent the computation from bombing. You can do this by concatenating a
zero-length string to the column. So the computation will become:

Trim(Right([COMMENTS] & "", Len([COMMENTS] & "") - InStrRev([COMMENTS] & "",
",")))

And you will not use a condition on the comments column so that you get
all rows.

Also, for some reason you are doing a grouping where you are not using
any aggregate functions (Max(), Min(), Avg(), etc.). So change that to a
regular select query instead of a summary query. Try:

SELECT dbo_CDS_AUDITTRAIL.AuditGamingdate,
dbo_PBT_PROMO_ADJUSTMENT.Player_ID, [dbo_CDS_PLAYER.lastname] & ", " &
[dbo_CDS_PLAYER.firstname] AS [Player Name], [dbo_CDS_USER.lastname] & " ," &
[dbo_CDS_USER.firstname] AS [Adjusted by],
Trim(Right([COMMENTS],Len([COMMENTS])-InStrRev([COMMENTS],","))) AS
ACTUAL_COMMENTS
FROM ((((dbo_CDS_AUDITDETAIL INNER JOIN dbo_CDS_AUDITTRAIL ON
dbo_CDS_AUDITDETAIL.AuditTrail_ID = dbo_CDS_AUDITTRAIL.AuditTrail_ID) INNER
JOIN dbo_CDS_USER ON dbo_CDS_AUDITTRAIL.User_ID = dbo_CDS_USER.User_ID) INNER
JOIN dbo_CDS_AUDITOBJECT ON dbo_CDS_AUDITTRAIL.AuditTrail_ID =
dbo_CDS_AUDITOBJECT.AuditTrail_ID) INNER JOIN dbo_CDS_PLAYER ON
dbo_CDS_AUDITOBJECT.Meta_ID = dbo_CDS_PLAYER.Player_ID) INNER JOIN
dbo_PBT_PROMO_ADJUSTMENT ON dbo_CDS_PLAYER.Player_ID =
dbo_PBT_PROMO_ADJUSTMENT.Player_ID
WHERE dbo_CDS_AUDITTRAIL.AuditGamingdate Between #9/3/2009# And
#9/4/2009# AND dbo_CDS_AUDITTRAIL.AuditEvent_ID=2630;

Hopefully I got that all right. Note that sometimes when trying to get
something to work, it is easier to attempt it in a simple query until you
figure it out and understand what is happening. Then transfer it into your
more complicated query. So if I did not get it right, try doing a simple
select of the computed column from the table that contains the comments.

Good Luck,

Clifford Bass

slotmgr70 said:
Hi Clifford,

I'm still getting an error, but it's probably because I'm not very good with
SQL scripts. Here's the script I have so far. Can you take a look at it and
suggest a solution:

SELECT dbo_CDS_AUDITTRAIL.AuditGamingdate,
dbo_PBT_PROMO_ADJUSTMENT.Player_ID, [dbo_CDS_PLAYER.lastname] & ", " &
[dbo_CDS_PLAYER.firstname] AS [Player Name], [dbo_CDS_USER.lastname] & " ," &
[dbo_CDS_USER.firstname] AS [Adjusted by]
FROM ((((dbo_CDS_AUDITDETAIL INNER JOIN dbo_CDS_AUDITTRAIL ON
dbo_CDS_AUDITDETAIL.AuditTrail_ID = dbo_CDS_AUDITTRAIL.AuditTrail_ID) INNER
JOIN dbo_CDS_USER ON dbo_CDS_AUDITTRAIL.User_ID = dbo_CDS_USER.User_ID) INNER
JOIN dbo_CDS_AUDITOBJECT ON dbo_CDS_AUDITTRAIL.AuditTrail_ID =
dbo_CDS_AUDITOBJECT.AuditTrail_ID) INNER JOIN dbo_CDS_PLAYER ON
dbo_CDS_AUDITOBJECT.Meta_ID = dbo_CDS_PLAYER.Player_ID) INNER JOIN
dbo_PBT_PROMO_ADJUSTMENT ON dbo_CDS_PLAYER.Player_ID =
dbo_PBT_PROMO_ADJUSTMENT.Player_ID
WHERE
(((dbo_CDS_AUDITDETAIL.Comments)=Trim(Right([COMMENTS],Len([COMMENTS])-InStrRev([COMMENTS],",")))))
GROUP BY dbo_CDS_AUDITTRAIL.AuditGamingdate,
dbo_PBT_PROMO_ADJUSTMENT.Player_ID, [dbo_CDS_PLAYER.lastname] & ", " &
[dbo_CDS_PLAYER.firstname], [dbo_CDS_USER.lastname] & " ," &
[dbo_CDS_USER.firstname], dbo_CDS_AUDITTRAIL.AuditEvent_ID
HAVING (((dbo_CDS_AUDITTRAIL.AuditGamingdate) Between #9/3/2009# And
#9/4/2009#) AND ((dbo_CDS_AUDITTRAIL.AuditEvent_ID)=2630));
 
Ad

Advertisements

C

Clifford Bass

Hi,

When your query results in duplicate rows, for whatever reason, and you
want to eliminate those duplicates you use the Distinct keyword:

select distinct columnname1, columname2...

You can set this either by typing it in after the select keywork in SQL
View or by right-clicking on the query while in Design View and choosing
properties. Click in a blank section of the background of the upper portion
of the query to make sure it is showing the properties of the query and not
of a table or a column. Change the Unique Values property to Yes. That
should avoid the error you were getting when trying to turn it back into a
summary query.

Clifford Bass
 
S

slotmgr70

I think it's working OK now. Thanks for all of your help. you kept it simple
for an amature like me.



Clifford Bass said:
Hi,

When your query results in duplicate rows, for whatever reason, and you
want to eliminate those duplicates you use the Distinct keyword:

select distinct columnname1, columname2...

You can set this either by typing it in after the select keywork in SQL
View or by right-clicking on the query while in Design View and choosing
properties. Click in a blank section of the background of the upper portion
of the query to make sure it is showing the properties of the query and not
of a table or a column. Change the Unique Values property to Yes. That
should avoid the error you were getting when trying to turn it back into a
summary query.

Clifford Bass

slotmgr70 said:
Hi Clifford,

It worked! I have the comment for each record.

But now I have multiple identical records for some transactions. That's why
I was grouping them so I would only get one record for each transaction. When
I tried to change the Player ID, Player Name, Adjusted by, and amount to
"group by" i get the following error:

"you tried to execute a query that does not include the specified expression
"AuditGamingDate" as part of an aggregate function"

Here is the revised SQL statement again:

SELECT dbo_CDS_AUDITTRAIL.AuditGamingdate,
dbo_PBT_PROMO_ADJUSTMENT.Player_ID, [dbo_CDS_PLAYER.lastname] & ", " &
[dbo_CDS_PLAYER.firstname] AS [Player Name], [dbo_CDS_USER.lastname] & " ," &
[dbo_CDS_USER.firstname] AS [Adjusted by], dbo_PBT_PROMO_ADJUSTMENT.Amount,
Trim(Right([COMMENTS],Len([COMMENTS])-InStrRev([COMMENTS],","))) AS
ACTUAL_COMMENTS
FROM ((((dbo_CDS_AUDITDETAIL INNER JOIN dbo_CDS_AUDITTRAIL ON
dbo_CDS_AUDITDETAIL.AuditTrail_ID = dbo_CDS_AUDITTRAIL.AuditTrail_ID) INNER
JOIN dbo_CDS_USER ON dbo_CDS_AUDITTRAIL.User_ID = dbo_CDS_USER.User_ID) INNER
JOIN dbo_CDS_AUDITOBJECT ON dbo_CDS_AUDITTRAIL.AuditTrail_ID =
dbo_CDS_AUDITOBJECT.AuditTrail_ID) INNER JOIN dbo_CDS_PLAYER ON
dbo_CDS_AUDITOBJECT.Meta_ID = dbo_CDS_PLAYER.Player_ID) INNER JOIN
dbo_PBT_PROMO_ADJUSTMENT ON dbo_CDS_PLAYER.Player_ID =
dbo_PBT_PROMO_ADJUSTMENT.Player_ID
WHERE (((dbo_CDS_AUDITTRAIL.AuditGamingdate) Between #9/3/2009# And
#9/4/2009#) AND ((dbo_CDS_AUDITTRAIL.AuditEvent_ID)=2630));
 
S

slotmgr70

Hate to be a pest, but I'm still getting duplicate records for some players.
It seems as if the query is creating "ghost" records.

For example, I have two records for a player that only had one transaction.
The "adjusted by" and "comment" fields are different for the two records, but
the other fields are identical.

Here is the SQL script once again:

SELECT DISTINCT dbo_PBT_PROMO_ADJUSTMENT.AdjustmentDate,
dbo_PBT_PROMO_ADJUSTMENT.Player_ID, [dbo_CDS_PLAYER.lastname] & ", " &
[dbo_CDS_PLAYER.firstname] AS [Player Name], [dbo_CDS_USER.lastname] & " ," &
[dbo_CDS_USER.firstname] AS [Adjusted by],
Sum(dbo_PBT_PROMO_ADJUSTMENT.Amount) AS SumOfAmount,
Trim(Right([COMMENTS],Len([COMMENTS])-InStrRev([COMMENTS],","))) AS
ACTUAL_COMMENTS
FROM ((((dbo_CDS_AUDITDETAIL INNER JOIN dbo_CDS_AUDITTRAIL ON
dbo_CDS_AUDITDETAIL.AuditTrail_ID = dbo_CDS_AUDITTRAIL.AuditTrail_ID) INNER
JOIN dbo_CDS_USER ON dbo_CDS_AUDITTRAIL.User_ID = dbo_CDS_USER.User_ID) INNER
JOIN dbo_CDS_AUDITOBJECT ON dbo_CDS_AUDITTRAIL.AuditTrail_ID =
dbo_CDS_AUDITOBJECT.AuditTrail_ID) INNER JOIN dbo_CDS_PLAYER ON
dbo_CDS_AUDITOBJECT.Meta_ID = dbo_CDS_PLAYER.Player_ID) INNER JOIN
dbo_PBT_PROMO_ADJUSTMENT ON dbo_CDS_PLAYER.Player_ID =
dbo_PBT_PROMO_ADJUSTMENT.Player_ID
WHERE (((dbo_CDS_AUDITTRAIL.AuditEvent_ID)=2630))
GROUP BY dbo_PBT_PROMO_ADJUSTMENT.AdjustmentDate,
dbo_PBT_PROMO_ADJUSTMENT.Player_ID, [dbo_CDS_PLAYER.lastname] & ", " &
[dbo_CDS_PLAYER.firstname], [dbo_CDS_USER.lastname] & " ," &
[dbo_CDS_USER.firstname],
Trim(Right([COMMENTS],Len([COMMENTS])-InStrRev([COMMENTS],",")))
HAVING (((dbo_PBT_PROMO_ADJUSTMENT.AdjustmentDate) Between #9/13/2009# And
#9/14/2009#));

Thanks,
 
Ad

Advertisements

C

Clifford Bass

Hi,

It looks like my reply never got through. Anyway, if something is
producing different results than expected, you will need to explore why that
is. Is is because of the data in the fields? Or is it because of the
presence of multiple rows in one of your tables? What I would suggest is
that you make a copy of the query and in the copy remove the grouping and the
distinct clause. Then run the query so you can see actual data that is being
pulled up. That should help determine why you are getting "duplicates". I
am guessing that one of your joins may be the cause of the "duplicates".
Maybe in some case there is one row on one side and several rows on the
other. To help figure out if this is the case, include all of the primary
key fields from all of the tables in the list of fields to display. Do some
sorting on each of the primary keys and look for places where the same value
shows up more than once.

Good Luck with That,

Clifford Bass
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top