reporting revisits from a query

[Guest]

I have a database that holds all our maintenance records by job card by location etc…. have tables for location, customer type, schedules and many more
I need to report the revisits to a single location we make each month – as a KPI. I can run a query from design view for selecting all the job cards between dates no problem but am finding it difficult to select only those within date parameter that appear more than once
Have tried to use the count (location) but every location is given a 1 so cannot use >1 criteria

Is there an easy query I can write. Or can you suggest an alternative way

 

[Jeff Boyce]

Telsys

Without a better idea of the actual data structure of the table (tables?)
you are querying, and the SQL statement you are now using, I could only make
guesses.

More info, please...

Jeff Boyce
<Access MVP>

 

[Gary Walter]

I have a database that holds all our maintenance records by job card by location

etc.. have tables for location, customer type, schedules and many more.

I need to report the revisits to a single location we make each month - as a KPI. I

can run a query from design view for selecting all the job cards between dates no
problem but am finding it difficult to select only those within date parameter that
appear more than once.

Have tried to use the count (location) but every location is given a 1 so cannot use >1 criteria.

Hi Telsys,

Without seeing your query or sample data and table structure....

(assuming your date parameter(s) limit to "a single year range or less")

--Group By [location] **and** Month([yourDateField])
--then add Count(*) to your query and set its criteria >1

Please post your query, table structure, and some sample data
if this is not clear.

Thanks,

Gary Walter

 

[John Viescas]

It's tough to guess without knowing the structure of your tables. It might
look something like:

SELECT JobCards.*
FROM JobCards
WHERE JobCard.JobCardID IN
(SELECT JobCardID
FROM JobCards
WHERE JobCards.JobDate Between #01 JAN 2004# And #31 JAN 2004#
GROUP BY JobCardID, Location
HAVING Count(*) > 1)

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)

I have a database that holds all our maintenance records by job card by

location etc.. have tables for location, customer type, schedules and many
more.

I need to report the revisits to a single location we make each month - as

a KPI. I can run a query from design view for selecting all the job cards
between dates no problem but am finding it difficult to select only those
within date parameter that appear more than once.

 

[Guest]

John, here is SQ
SELECT TblJobCard.JobNo, TblJobCard.OrderNo, TblJobCard.ComplainantDate, TblJobCard.AssetNo, TblJobCard.Requirement, TblJobCard.Details, TblJobCard.LocationN
FROM TblJobCard INNER JOIN TblLocation ON TblJobCard.LocationNO = TblLocation.LocationN
GROUP BY TblJobCard.JobNo, TblJobCard.OrderNo, TblJobCard.ComplainantDate, TblJobCard.AssetNo, TblJobCard.Requirement, TblJobCard.Details, TblJobCard.LocationN
HAVING (((TblJobCard.ComplainantDate) Between [start date] And [end date]))

this returns all the jobs we have completed within the date range but doesn't yet JUST return the revisits (duplicate record of unique location id - this has its own table) . Have tired the count you suggest but doesn't return any data. I dont use the SQL I am only using the design form process. Perhaps I am doing something wrong

----- John Viescas wrote: ----

It's tough to guess without knowing the structure of your tables. It migh
look something like

SELECT JobCards.
FROM JobCard
WHERE JobCard.JobCardID I
(SELECT JobCardI
FROM JobCard
WHERE JobCards.JobDate Between #01 JAN 2004# And #31 JAN 2004
GROUP BY JobCardID, Locatio
HAVING Count(*) > 1

--
John Viescas, autho
"Microsoft Office Access 2003 Inside Out
"Running Microsoft Access 2000
"SQL Queries for Mere Mortals
http://www.viescas.com
(Microsoft Access MVP since 1993

I have a database that holds all our maintenance records by job card b

location etc.. have tables for location, customer type, schedules and man
more

I need to report the revisits to a single location we make each month - a

a KPI. I can run a query from design view for selecting all the job card
between dates no problem but am finding it difficult to select only thos
within date parameter that appear more than once

 

[John Viescas]

What is the Primary Key of TblJobCard? I may need to know that to solve
your problem. Or, do you need to see records where a particular JobNo
occurs more than once in the date span?
If so, the solution is:

PARAMETERS [start date] datetime, [end date] datetime;
SELECT TblJobCard.JobNo, TblJobCard.OrderNo, TblJobCard.ComplainantDate,
TblJobCard.AssetNo, TblJobCard.Requirement, TblJobCard.Details,
TblJobCard.LocationNO
FROM TblJobCard INNER JOIN TblLocation ON TblJobCard.LocationNO =
TblLocation.LocationNO
WHERE TblJobCard.ComplainantDate Between [start date] And [end date]
AND 1 < (SELECT Count(*) FROM TblJobCard AS T2
WHERE T2.JobNo = TblJobCard.JobNo AND
T2.ComplainantDate Between [start date] And [end date]);

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)

John, here is SQL
SELECT TblJobCard.JobNo, TblJobCard.OrderNo, TblJobCard.ComplainantDate,

TblJobCard.AssetNo, TblJobCard.Requirement, TblJobCard.Details,
TblJobCard.LocationNO

FROM TblJobCard INNER JOIN TblLocation ON TblJobCard.LocationNO = TblLocation.LocationNO
GROUP BY TblJobCard.JobNo, TblJobCard.OrderNo, TblJobCard.ComplainantDate,

TblJobCard.AssetNo, TblJobCard.Requirement, TblJobCard.Details,
TblJobCard.LocationNO

HAVING (((TblJobCard.ComplainantDate) Between [start date] And [end date]));

this returns all the jobs we have completed within the date range but

doesn't yet JUST return the revisits (duplicate record of unique location
id - this has its own table) . Have tired the count you suggest but doesn't
return any data. I dont use the SQL I am only using the design form process.
Perhaps I am doing something wrong.

 

[Guest]

John the primary key is JobNo and yes I do need to see particular job cards within a date span. Have tried below solution but returns zero dat

Thank

----- John Viescas wrote: ----

What is the Primary Key of TblJobCard? I may need to know that to solv
your problem. Or, do you need to see records where a particular JobN
occurs more than once in the date span
If so, the solution is

PARAMETERS [start date] datetime, [end date] datetime
SELECT TblJobCard.JobNo, TblJobCard.OrderNo, TblJobCard.ComplainantDate
TblJobCard.AssetNo, TblJobCard.Requirement, TblJobCard.Details
TblJobCard.LocationN
FROM TblJobCard INNER JOIN TblLocation ON TblJobCard.LocationNO
TblLocation.LocationN
WHERE TblJobCard.ComplainantDate Between [start date] And [end date
AND 1 < (SELECT Count(*) FROM TblJobCard AS T
WHERE T2.JobNo = TblJobCard.JobNo AN
T2.ComplainantDate Between [start date] And [end date])

--
John Viescas, autho
"Microsoft Office Access 2003 Inside Out
"Running Microsoft Access 2000
"SQL Queries for Mere Mortals
http://www.viescas.com
(Microsoft Access MVP since 1993

SELECT TblJobCard.JobNo, TblJobCard.OrderNo, TblJobCard.ComplainantDate

TblJobCard.AssetNo, TblJobCard.Requirement, TblJobCard.Details
TblJobCard.LocationN

FROM TblJobCard INNER JOIN TblLocation ON TblJobCard.LocationNO TblLocation.LocationN
GROUP BY TblJobCard.JobNo, TblJobCard.OrderNo, TblJobCard.ComplainantDate

TblJobCard.AssetNo, TblJobCard.Requirement, TblJobCard.Details
TblJobCard.LocationN

HAVING (((TblJobCard.ComplainantDate) Between [start date] And [en date]))

this returns all the jobs we have completed within the date range bu

doesn't yet JUST return the revisits (duplicate record of unique locatio
id - this has its own table) . Have tired the count you suggest but doesn'
return any data. I dont use the SQL I am only using the design form process
Perhaps I am doing something wrong

 

[John Viescas]

That should work. Basically, the subquery:

(SELECT Count(*) FROM TblJobCard AS T2
WHERE T2.JobNo = TblJobCard.JobNo AND
T2.ComplainantDate Between [start date] And [end date])

... returns the number of job cards for the current job record between the
two dates. If there is more than one in the date span, you should see those
job cards in the outer query.

Are you sure you have duplicate records within the date span you entered?
You can check it with this query:

PARAMETERS [start date] datetime, [end date] datetime;
SELECT JobNo, Count(*) As CountJobs
FROM TblJobCard
WHERE ComplainantDate Between [start date] And [end date]
GROUP BY JobNo
HAVING Count(*) > 1

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)

John the primary key is JobNo and yes I do need to see particular job

cards within a date span. Have tried below solution but returns zero data

Thanks

----- John Viescas wrote: -----

What is the Primary Key of TblJobCard? I may need to know that to solve
your problem. Or, do you need to see records where a particular JobNo
occurs more than once in the date span?
If so, the solution is:

PARAMETERS [start date] datetime, [end date] datetime;
SELECT TblJobCard.JobNo, TblJobCard.OrderNo, TblJobCard.ComplainantDate,
TblJobCard.AssetNo, TblJobCard.Requirement, TblJobCard.Details,
TblJobCard.LocationNO
FROM TblJobCard INNER JOIN TblLocation ON TblJobCard.LocationNO =
TblLocation.LocationNO
WHERE TblJobCard.ComplainantDate Between [start date] And [end date]
AND 1 < (SELECT Count(*) FROM TblJobCard AS T2
WHERE T2.JobNo = TblJobCard.JobNo AND
T2.ComplainantDate Between [start date] And [end date]);

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)

SELECT TblJobCard.JobNo, TblJobCard.OrderNo,

TblJobCard.ComplainantDate,
TblJobCard.AssetNo, TblJobCard.Requirement, TblJobCard.Details,
TblJobCard.LocationNO

FROM TblJobCard INNER JOIN TblLocation ON TblJobCard.LocationNO = TblLocation.LocationNO
GROUP BY TblJobCard.JobNo, TblJobCard.OrderNo,

TblJobCard.ComplainantDate,
TblJobCard.AssetNo, TblJobCard.Requirement, TblJobCard.Details,
TblJobCard.LocationNO

HAVING (((TblJobCard.ComplainantDate) Between [start date] And [end date]));

this returns all the jobs we have completed within the date range

but
doesn't yet JUST return the revisits (duplicate record of unique location
id - this has its own table) . Have tired the count you suggest but doesn't
return any data. I dont use the SQL I am only using the design form process.
Perhaps I am doing something wrong. tables. It
might

look something like:
FROM JobCards
WHERE JobCard.JobCardID IN
(SELECT JobCardID
FROM JobCards
WHERE JobCards.JobDate Between #01 JAN 2004# And #31 JAN 2004#
GROUP BY JobCardID, Location
HAVING Count(*) > 1)
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
card by
location etc.. have tables for location, customer type,

schedules and
many

more. month - as
a KPI. I can run a query from design view for selecting all

the job
cards

between dates no problem but am finding it difficult to select

only

those
within date parameter that appear more than once. a 1 so
cannot use >1 criteria.

 

[Guest]

Yes this works when I changed the data to search for duplicate LocationNO not jobNo which are sequential records it works fine

We are a telemetry company in the Bay Of Plenty, NZ and you have been extremely helpful - thankyou so much for your help
NB. visited your web and notes the publications you have listed for helpful reading

Once again thanks for your help.