SQL query

T

tsluu

tblItem
Item Code | Desc |

tblTran
Tran_ID | Date | Item Code | Qty |


I need a query to show the following:

tblItem.Item Code | tblTran.Date | tblTran.Qty

1. All rows in tblItem whether there are corresponding foreign keys in
tblTran or not
2. Filter by required Date
3. Sum on tblTran.Qty

so as an example

tblItem
1 | One
2 | Two
3 | Three

tblTran
1 | 1/01/2010 | 1 | 2
2 | 2/01/2010 | 2 | 2
3 | 3/01/2010 | 3 | 2
4 | 3/01/2010 | 3 | 2
5 | 4/01/2010 | 1 | 2

results: filter on date (3/01/2010)
tblItem.Item Code | tblTran.Date | tblTran.Qty
1 | Null | Null
2 | Null | Null
3 | 3/01/2010 | 4
 
K

KARL DEWEY

Try this SQL --
SELECT tblItem.[Item Code], tblTran.Date, Sum(tblTran.Qty) AS Total_Trans
FROM tblItem LEFT JOIN tblTran ON tblItem.[Item Code] = tblTran.[Item Code]
WHERE tblTran.Date = CVDate([Enter date - 12/25/2009])
GROUP BY tblItem.[Item Code], tblTran.Date;

Or in design view place both tables in the space above the grid with tblItem
on the left. Click on tblItem field Item Code and drag to tblTran. Click on
the connecting line and then double click. Select the option to show all
records of tblItem and only those of tblTran that match.

Drag the required fields to the first row of the grid. Click on the icon
that looks like an 'M' on its side. Change GROUP BY under QTY to Sum.

Type ' CVDate([Enter date - 12/25/2009])' without quotes in the CRITERIA row
below the date field.
 
S

sandypeter111

tsluu;3630197 said:
tblItem
Item Code | Desc |

tblTran
Tran_ID | Date | Item Code | Qty |


I need a query to show the following:

tblItem.Item Code | tblTran.Date | tblTran.Qty

1. All rows in tblItem whether there are corresponding foreign keys in
tblTran or not
2. Filter by required Date
3. Sum on tblTran.Qty

so as an example

tblItem
1 | One
2 | Two
3 | Three

tblTran
1 | 1/01/2010 | 1 | 2
2 | 2/01/2010 | 2 | 2
3 | 3/01/2010 | 3 | 2
4 | 3/01/2010 | 3 | 2
5 | 4/01/2010 | 1 | 2

results: filter on date (3/01/2010)
tblItem.Item Code | tblTran.Date | tblTran.Qty
1 | Null | Null
2 | Null | Null
3 | 3/01/2010 | 4

If your problem involves software, hardware or service brands, most rep
will pass the buck. Then you start again. Add to the mix som
manufacturers charge for support. IT support is the way to keep all o
your tech in tune through a subscription-based tech support center
 
J

John Spencer

Since you want to filter by date you will need two queries. First query
SELECT Tran_Id, [Date], Qty, [Item Code]
FROM tblTran
WHERE [Date] = Cdate([Enter Date])

NOW you use that saved query as a table in the next query

SELECT tblItem.[Item Code]
, qSavedQuery.[Date]
, Sum(qSavedQuery.Qty) as TotalAmount
FROM tblItem LEFT JOIN qSavedQuery
On TblItem.[Item Code] = qSavedQuery.[Item Code]
GROUP BY tblItem.[Item Code], qSavedQuery.[Date]

If your table and field names followed the naming guidelines (Letters,
Numbers, and the underscore character only and NO reserved word, you could
accomplish this in one query that used a sub-query in the FROM clause.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
 
T

tsluu

Thanks John.

For my case it will be a saved cross-tab query rather than a select query.

John Spencer said:
Since you want to filter by date you will need two queries. First query
SELECT Tran_Id, [Date], Qty, [Item Code]
FROM tblTran
WHERE [Date] = Cdate([Enter Date])

NOW you use that saved query as a table in the next query

SELECT tblItem.[Item Code]
, qSavedQuery.[Date]
, Sum(qSavedQuery.Qty) as TotalAmount
FROM tblItem LEFT JOIN qSavedQuery
On TblItem.[Item Code] = qSavedQuery.[Item Code]
GROUP BY tblItem.[Item Code], qSavedQuery.[Date]

If your table and field names followed the naming guidelines (Letters,
Numbers, and the underscore character only and NO reserved word, you could
accomplish this in one query that used a sub-query in the FROM clause.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
tblItem
Item Code | Desc |

tblTran
Tran_ID | Date | Item Code | Qty |


I need a query to show the following:

tblItem.Item Code | tblTran.Date | tblTran.Qty

1. All rows in tblItem whether there are corresponding foreign keys in
tblTran or not
2. Filter by required Date
3. Sum on tblTran.Qty

so as an example

tblItem
1 | One
2 | Two
3 | Three

tblTran
1 | 1/01/2010 | 1 | 2
2 | 2/01/2010 | 2 | 2
3 | 3/01/2010 | 3 | 2
4 | 3/01/2010 | 3 | 2
5 | 4/01/2010 | 1 | 2

results: filter on date (3/01/2010)
tblItem.Item Code | tblTran.Date | tblTran.Qty
1 | Null | Null
2 | Null | Null
3 | 3/01/2010 | 4
.
 

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