pr**********@gmail.com wrote:
Hi all,
I am not so good in queries. here is my problem
2 tables to store the received items (fabric)-- inwardmaster,
inwarddetails
and after some processing (Dyeing)
the items were deliverd and stored in tables -- deliverymaster,
deliverydetails
some times it may me returned without processing (grey fabric) and
stored in -- returnmaster and returndetails
lng_LI_RecordNo is the primary and used in delivery and return tables.
i want to take a report like this
STATUS REPORT BETWEEN 01/9/2005 AND 30/9/2005
--------------------------------------------------------------------------------
CUSTOMER NAME OPENING BALANCE INWARD QTY DELIVERD QTY RETURN QTY
CLOS.BAL
--------------------------------------------------------------------------------
i tried to calculate like this
first get the customers who gave fabric < 01/09/2005
start customer loop
get total inward qty of customer < 01/09/2005
get total delivery qty of customer < 01/09/2005
get total return qty of customer < 01/09/2005
opening balance = total inward qty - (devliered qty + retured qty)
get total inward qty of customer between 01/09/2005 and 30/09/2005
get total delivery qty of customer < 01/09/2005 and 30/09/2005
get total return qty of customer < 01/09/2005 and 30/09/2005
Closing balance = opening balance + total inward qty - (devliered qty
+ retured qty)
update values in new table and show it in crystal report.
end customer loop
with this method it takes more than 45 minutes to get the report, pls
help me to write a single query which does all this or help me the
other way to get the report fast. i use vb6 with access 2000.
pls help me.
prabhu.
45 Minutes!!!! Wow! That doesn't say much for Crystal Reports.
Here's a method I may use in Access.
I may have a form called CallRpt. In it I would have 3 text fields that
store dates; LessThanDate, BetweenFromDate, and BetweenToDate.
I would then create 6 queries that do totalling. Ex:
Select inwardmaster.customerid,
Sum(inwardDetail.inwardqty) As InDelTot _
from inwardmaster INNER JOIN inwardDetail On _
inwardmaster.lng_LI_RecordNo = _
inwardDetail.lng_LI_RecordNo _
Where inwardDetail.InwardDate < LessThanDate _
Group By inwardmaster.customerid
This will select all customers with records less than the LessThanDate.
Select inwardmaster.customerid,
Sum(inwardDetail.inwardqty) As InDelTot _
from inwardmaster INNER JOIN inwardDetail On _
inwardmaster.lng_LI_RecordNo = _
inwardDetail.lng_LI_RecordNo _
Where inwardDetail.InwardDate Between _
[Forms]![CallRpt]![FromDate] And _
[Forms]![CallRpt]![ToDate] _
Group By inwardmaster.customerid
This query will select and total all qty by customerid with dates
between the From/To Dates.
Now do the same thing for Returns and Deleivery.
You now have 6 queries with the sums for all six Totals that you require.
You now need a "master" query to link the customerID to all of the other
queries. Perhaps one of these 6 can be the master. Basically, you want
to only select customer records that has a record in 1 of these 6 queries.
Lets use the customer table as the master. Drop that and the 6 queries
into the query builder. Now create a relationship line of customer Id
to all 6 tables. Right click the relationship line and set it to All
records in Customer and only those that match for the table in the
relationship.
Now you can filter this table down where you check the value of the Sum.
I would use IsNull. Ex:
Not IsNull(InDelTot) Or Not IsNull(ReturnTot) Or Not
IsNull(DeliveryTot)....etc.
Now you have a query that will select only customers that have an
inward, delivery, or return record that meets the time frames specified.
If the customr has no records, all of the "sum" fields will be null.
From here you can use the report writer to perform calculations on the
fields.
This will bring down the reporting function from 45 minutes to about 2
seconds.
