help with inner join where between fromdate and todate sql query

  • Thread starter Thread starter Silvester
  • Start date Start date
S

Silvester

Hi,

I have a simple billing app with 2 normalised tables.

1. ItemDetails
with details of ProductID, ProdDescription, etc
2. Transactions
with ProductID, SaleQty, SaleDate, Cashier

I need a summary report with a FROMdate and a TOdate that the user can
select. The report will be grouped by ProductID with ProdDescription,
SaleQty, SaleDate, Cashier.

Can someone please help me with sql including the INNER JOIN and WHERE
between FROMDATE AND TODate clause.

I have been able to get the report to work fine with a simple inner join but
the where between FromDate and ToDate is giving me problems when added.

Do I need to use querydef ?

No air code, pls !

Thanks very much for the help.

How can
 
Silvester said:
Hi,

I have a simple billing app with 2 normalised tables.
Click to expand...

Normalised tables said:
1. ItemDetails
with details of ProductID, ProdDescription, etc
2. Transactions
with ProductID, SaleQty, SaleDate, Cashier

I need a summary report with a FROMdate and a TOdate that the user can
select. The report will be grouped by ProductID with ProdDescription,
SaleQty, SaleDate, Cashier.

Can someone please help me with sql including the INNER JOIN and WHERE
between FROMDATE AND TODate clause.

I have been able to get the report to work fine with a simple inner
join but the where between FromDate and ToDate is giving me problems
when added.

Do I need to use querydef ?

No air code, pls !
Click to expand...

Hmph! That last is not a reasonable request. In effect, you're asking
the community to do your job for you. It's your responsibility to learn
enough to make use of the suggestions you are given.

So, here's some "air design" and "air SQL":

Have a form from which you run the report, with text boxes "txtFromDate"
and "txtToDate", and a command button to run the report. Code in the
button's Click event should verify that the from and to dates have been
filled in before actually opening the report. Suppose we call this form
"frmReportDates".

Set the report's recordsource query to something like this:

SELECT
Transactions.ProductID,
ItemDetails.ProdDescription,
SaleQty,
SaleDate,
Cashier
FROM
Transactions
INNER JOIN
ItemDetails
ON Transactions.ProductID = ItemDetails.ProductID
WHERE
SaleDate Between [Forms]![frmReportDates]![txtFromDate]
And [Forms]![frmReportDates]![txtToDate];

In your report design, use the Sorting and Grouping dialog to group by
ProductID and sort, if you like, by SaleDate.
 
Sorry it sounded like I asked others to do my job for me... after mentioning
all that I had already done in my earlier posting. We come to these
newsgroups because, having done what we can, we are facing difficulties.

What you have helped me with is to understand I have to include the sql
statement in the recordsource of the report I have already created and for
that I thank you.


Dirk Goldgar said:
Silvester said:
Hi,

I have a simple billing app with 2 normalised tables.
Click to expand...

Normalised tables said:
1. ItemDetails
with details of ProductID, ProdDescription, etc
2. Transactions
with ProductID, SaleQty, SaleDate, Cashier

I need a summary report with a FROMdate and a TOdate that the user can
select. The report will be grouped by ProductID with ProdDescription,
SaleQty, SaleDate, Cashier.

Can someone please help me with sql including the INNER JOIN and WHERE
between FROMDATE AND TODate clause.

I have been able to get the report to work fine with a simple inner
join but the where between FromDate and ToDate is giving me problems
when added.

Do I need to use querydef ?

No air code, pls !
Click to expand...

Hmph! That last is not a reasonable request. In effect, you're asking
the community to do your job for you. It's your responsibility to learn
enough to make use of the suggestions you are given.

So, here's some "air design" and "air SQL":

Have a form from which you run the report, with text boxes "txtFromDate"
and "txtToDate", and a command button to run the report. Code in the
button's Click event should verify that the from and to dates have been
filled in before actually opening the report. Suppose we call this form
"frmReportDates".

Set the report's recordsource query to something like this:

SELECT
Transactions.ProductID,
ItemDetails.ProdDescription,
SaleQty,
SaleDate,
Cashier
FROM
Transactions
INNER JOIN
ItemDetails
ON Transactions.ProductID = ItemDetails.ProductID
WHERE
SaleDate Between [Forms]![frmReportDates]![txtFromDate]
And [Forms]![frmReportDates]![txtToDate];

In your report design, use the Sorting and Grouping dialog to group by
ProductID and sort, if you like, by SaleDate.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
Back
Top