SQL Parameter Query

[Guest]

Hello All,
I just can't get my head around this one. I know there is an easy way to do
this with SQL but I don't know how to write the string.

I have a table (IC) that has a listing of changes made to all Interpreters.
I record the following:
ChangeID - autonumber uniquely identifying the change
CustID - ID Number uniquely identifying the Interpreter
ChangeDate - date the change was made
CertificationType - this is the certification type that was changed, there
are six different certification categories
NewValue - the value the CertificationType was changed to

I want to generate a list of each of the Interpreters (CustID) by their
latest certification (CertificationType) based on the latest date for that
(CertificationType) (by ChangeDate) showing the (NewValue) it was changed to.

Any ideas are welcome including becoming a hermit.

Thanks in advance.

Brian

 

[Jeff Boyce]

Brian

Are you saying you want to find the Maximum [ChangeDate] for each
Interpreters (?[CustID]), and note the [CertType] and [NewValue]?

This sounds very much like a Totals query -- check Access HELP on the topic.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Thanks Jeff,
It's almost a Totals query, but i need the data broken up more by [CertType]
ie each CustID can have up to six different [CertTypes].

Brian

Brian

Are you saying you want to find the Maximum [ChangeDate] for each
Interpreters (?[CustID]), and note the [CertType] and [NewValue]?

This sounds very much like a Totals query -- check Access HELP on the topic.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hello All,
I just can't get my head around this one. I know there is an easy way to
do
this with SQL but I don't know how to write the string.

I have a table (IC) that has a listing of changes made to all
Interpreters.
I record the following:
ChangeID - autonumber uniquely identifying the change
CustID - ID Number uniquely identifying the Interpreter
ChangeDate - date the change was made
CertificationType - this is the certification type that was changed, there
are six different certification categories
NewValue - the value the CertificationType was changed to

I want to generate a list of each of the Interpreters (CustID) by their
latest certification (CertificationType) based on the latest date for that
(CertificationType) (by ChangeDate) showing the (NewValue) it was changed
to.

Any ideas are welcome including becoming a hermit.

Thanks in advance.

Brian

 

[Jeff Boyce]

Group by CustID, Group by CertType...

You are not limited to one GroupBy.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Thanks Jeff,
It's almost a Totals query, but i need the data broken up more by
[CertType]
ie each CustID can have up to six different [CertTypes].

Brian

Brian

Are you saying you want to find the Maximum [ChangeDate] for each
Interpreters (?[CustID]), and note the [CertType] and [NewValue]?

This sounds very much like a Totals query -- check Access HELP on the
topic.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hello All,
I just can't get my head around this one. I know there is an easy way
to
do
this with SQL but I don't know how to write the string.

I have a table (IC) that has a listing of changes made to all
Interpreters.
I record the following:
ChangeID - autonumber uniquely identifying the change
CustID - ID Number uniquely identifying the Interpreter
ChangeDate - date the change was made
CertificationType - this is the certification type that was changed,
there
are six different certification categories
NewValue - the value the CertificationType was changed to

I want to generate a list of each of the Interpreters (CustID) by their
latest certification (CertificationType) based on the latest date for
that
(CertificationType) (by ChangeDate) showing the (NewValue) it was
changed
to.

Any ideas are welcome including becoming a hermit.

Thanks in advance.

Brian