How do I create a weekly transaction query?

  • Thread starter Thread starter Guest
  • Start date Start date
Show us the SQL for your daily query. Open the query in design view. Next go
to View, SQL View and copy and past it here. Information on primary keys and
relationships would be a nice touch too.
 
Attached is my sql and information on the two tables.

SELECT [Production Ticket Query].jobNumber AS [Job Number], [Project
Information].jobName AS [Job Name], Count([Production Ticket
Query].pieceMark) AS [Total Tikckets], Sum([Production Ticket Query].[Total
Sq Ft]) AS [Total Sq Ft], Sum([Production Ticket Query].quantity) AS [Total
Pieces], [Production Ticket Query].issuedProduction AS [Issued for Production]
FROM [Production Ticket Query] INNER JOIN [Project Information] ON
[Production Ticket Query].jobNumber = [Project Information].jobNumber
GROUP BY [Production Ticket Query].jobNumber, [Project Information].jobName,
[Production Ticket Query].issuedProduction
HAVING ((([Project Information].jobName)=[Forms]![Weekly Sq Ft
Form]![ProjectName]) AND (([Production Ticket Query].issuedProduction)
Between [Forms]![Weekly Sq Ft Form]![FirstDate] And [Forms]![Weekly Sq Ft
Form]![SecondDate]));

Project Information Table
-jobNumber -Primary Key
-jobName
-plantLocation
-jobSquareFootage

Production Tickets Table
-ID - Primary Key
-pieceMark
-squareFootage
-quantity
-weight
-jobNumber
-issuedProduction
 
Try this:

SELECT PTQ.jobNumber AS [Job Number],
PI.jobName AS [Job Name],
Count(PTQ.pieceMark) AS [Total Tikckets],
Sum(PTQ.[Total Sq Ft]) AS [Total Sq Ft],
Sum(PTQ.quantity) AS [Total Pieces],
DatePart("ww", PTQ.issuedProduction) AS [Issued for Production]
FROM [Production Ticket Query] AS PTQ
INNER JOIN [Project Information] AS PI
ON PTQ.jobNumber = PI.jobNumber
GROUP BY PTQ.jobNumber,
PI.jobName,
DatePart("ww", PTQ.issuedProduction)
HAVING PI.jobName )= [Forms]![Weekly Sq Ft Form]![ProjectName])
AND DatePart("ww", PTQ.issuedProduction)
Between DatePart("ww", [Forms]![Weekly Sq Ft Form]![FirstDate])
And DatePart("ww", [Forms]![Weekly Sq Ft Form]![SecondDate];)

--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


legacy said:
Attached is my sql and information on the two tables.

SELECT [Production Ticket Query].jobNumber AS [Job Number], [Project
Information].jobName AS [Job Name], Count([Production Ticket
Query].pieceMark) AS [Total Tikckets], Sum([Production Ticket Query].[Total
Sq Ft]) AS [Total Sq Ft], Sum([Production Ticket Query].quantity) AS [Total
Pieces], [Production Ticket Query].issuedProduction AS [Issued for Production]
FROM [Production Ticket Query] INNER JOIN [Project Information] ON
[Production Ticket Query].jobNumber = [Project Information].jobNumber
GROUP BY [Production Ticket Query].jobNumber, [Project Information].jobName,
[Production Ticket Query].issuedProduction
HAVING ((([Project Information].jobName)=[Forms]![Weekly Sq Ft
Form]![ProjectName]) AND (([Production Ticket Query].issuedProduction)
Between [Forms]![Weekly Sq Ft Form]![FirstDate] And [Forms]![Weekly Sq Ft
Form]![SecondDate]));

Project Information Table
-jobNumber -Primary Key
-jobName
-plantLocation
-jobSquareFootage

Production Tickets Table
-ID - Primary Key
-pieceMark
-squareFootage
-quantity
-weight
-jobNumber
-issuedProduction



Jerry Whittle said:
Show us the SQL for your daily query. Open the query in design view. Next go
to View, SQL View and copy and past it here. Information on primary keys and
relationships would be a nice touch too.
Click to expand...
Click to expand...
 
Jerry,
It worked, Thanks. Now I want to take it a step further, on the column
Issued for Production instead of showing the week number as shown below, I
would like to display the week ending date (ie. Issued For Production 9 to
display 3/3/2007).

Thanks again

Job Number | Job Name | Total Tickets | Total Sq Ft | Issued For Production
27140 | Ortiz | 2 | 2 | 9
| | 5 | 7 |
10
| | 4 | 4 |
11

Jerry Whittle said:
Try this:

SELECT PTQ.jobNumber AS [Job Number],
PI.jobName AS [Job Name],
Count(PTQ.pieceMark) AS [Total Tikckets],
Sum(PTQ.[Total Sq Ft]) AS [Total Sq Ft],
Sum(PTQ.quantity) AS [Total Pieces],
DatePart("ww", PTQ.issuedProduction) AS [Issued for Production]
FROM [Production Ticket Query] AS PTQ
INNER JOIN [Project Information] AS PI
ON PTQ.jobNumber = PI.jobNumber
GROUP BY PTQ.jobNumber,
PI.jobName,
DatePart("ww", PTQ.issuedProduction)
HAVING PI.jobName )= [Forms]![Weekly Sq Ft Form]![ProjectName])
AND DatePart("ww", PTQ.issuedProduction)
Between DatePart("ww", [Forms]![Weekly Sq Ft Form]![FirstDate])
And DatePart("ww", [Forms]![Weekly Sq Ft Form]![SecondDate];)

--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


legacy said:
Attached is my sql and information on the two tables.

SELECT [Production Ticket Query].jobNumber AS [Job Number], [Project
Information].jobName AS [Job Name], Count([Production Ticket
Query].pieceMark) AS [Total Tikckets], Sum([Production Ticket Query].[Total
Sq Ft]) AS [Total Sq Ft], Sum([Production Ticket Query].quantity) AS [Total
Pieces], [Production Ticket Query].issuedProduction AS [Issued for Production]
FROM [Production Ticket Query] INNER JOIN [Project Information] ON
[Production Ticket Query].jobNumber = [Project Information].jobNumber
GROUP BY [Production Ticket Query].jobNumber, [Project Information].jobName,
[Production Ticket Query].issuedProduction
HAVING ((([Project Information].jobName)=[Forms]![Weekly Sq Ft
Form]![ProjectName]) AND (([Production Ticket Query].issuedProduction)
Between [Forms]![Weekly Sq Ft Form]![FirstDate] And [Forms]![Weekly Sq Ft
Form]![SecondDate]));

Project Information Table
-jobNumber -Primary Key
-jobName
-plantLocation
-jobSquareFootage

Production Tickets Table
-ID - Primary Key
-pieceMark
-squareFootage
-quantity
-weight
-jobNumber
-issuedProduction



Jerry Whittle said:
Show us the SQL for your daily query. Open the query in design view. Next go
to View, SQL View and copy and past it here. Information on primary keys and
relationships would be a nice touch too.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

:

I would like to display results in a weekly basis instead of daily.
Click to expand...
Click to expand...
Click to expand...
 
Jerry,
It worked, Thanks. Now I want to take it a step further, on the column
Issued for Production instead of showing the week number as shown below, I
would like to display the week ending date (ie. Issued For Production 9 to
display 3/3/2007).
Click to expand...

try:
Dateadd("ww",Datediff("ww",0,PTQ.issuedProduction),0) AS [Week Ending]
 
Michael,
Could you please guide me in the right direction? I tried to input the
function, but I received an error, it stated that "you tried to execute a
query that does not include the specified expression as part of an aggregate
function. Any advise will help,
Thanks

Michael Gramelspacher said:
Jerry,
It worked, Thanks. Now I want to take it a step further, on the column
Issued for Production instead of showing the week number as shown below, I
would like to display the week ending date (ie. Issued For Production 9 to
display 3/3/2007).
Click to expand...

try:
Dateadd("ww",Datediff("ww",0,PTQ.issuedProduction),0) AS [Week Ending]
Click to expand...
 
Michael,
Could you please guide me in the right direction? I tried to input the
function, but I received an error, it stated that "you tried to execute a
query that does not include the specified expression as part of an aggregate
function. Any advise will help,
Thanks

Michael Gramelspacher said:
Jerry,
It worked, Thanks. Now I want to take it a step further, on the column
Issued for Production instead of showing the week number as shown below, I
would like to display the week ending date (ie. Issued For Production 9 to
display 3/3/2007).
Click to expand...

try:
Dateadd("ww",Datediff("ww",0,PTQ.issuedProduction),0) AS [Week Ending]
Click to expand...
Click to expand...
Yes, just add it also to the GROUP BY clause, but omit the AS [Week
Ending].

GROUP BY Dateadd("ww",Datediff("ww",0,PTQ.issuedProduction),0)
 

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

Similar Threads

Display columns without data in cross tab query 4
Week, monthly, quarterly 2
How to display a date field in a 4 weekly cycle 5
How do create a query that groups by weeks? (not a report) 2
Loop through query and export records on a date by date basis asseperate files... 5
Weekly count 3
create a query that will pull data from last week information only 3
Month, DATE, Week 7

Back
Top