SQL query

[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

 

[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.

 

[sandypeter111]

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

 

[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

 

[tsluu]

Thanks John.

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

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

.