PCFormat Forum Archive

Hi I'm building a report that shows the following

Item , Item Desc , Qty Sold , QTY On hand, QTY Backorder.

The report run's year to May 2009 based on the sale date.

Ive create a seperate Sales report , Stock Onhand & Backorder report.
I then imported the 3 reports into an Access database then use crystal reports to
report from it.

All the reports are now a table.

Sales Table
Item, Qty Sold

Qty On hand
Item, QtyOnhand

Backorders Table
Item, QtyBackorder

And im linking the 3 together on the Item (Which is unique)

Sales Item is linked to Qty Onhand and Backorders is also linked to the Sales tables Item Field.

The Problem is.

Some items in the Sales table is not pressent cus its based on date, which is pressent on Bac orders and Same with QtyOnhand some items is not pressent on sales where there is stock available , hope it makes sense.

So when these are linked the Join matches only what it can find, render ing my report useless.

I can set the type of joining in Crystal Reports but ive tried all the combinations and it still doesnt work.
Can any one help me ?

I think an OUTER JOIN of one kind or another may be your friend here.

I seriously thought that this is going to be a thread about sore knees, elbows and hips when I read "Joint Problems".

*sigh*

Yes, an inner join only takes records where it finds a match in both tables. Interesting read.

Not used Crystal but can help you in Access if you want. Ronny is right - you need to set your outer join to be everything in Sales table visible. You never mentioned any dates in your fields though.

Which table do you want to see all the records of?

Rather keep your calculations and stuff to a minimum in Crystal. Do Group By's and Order By's and totalling etc in Crystal. Crystal gets clumsy with "raw" data and what ever you want to do, it's so much more easier and versatile in SQL. Except off course displaying the actual report

I changed the topic to SQL Join Problems

And i made quickly made this macro.

Dont laugh please.

I have 4 sheets SALES, BACKORDERS and STOCKONHAND & COMBINED.

The Stock on hand is all items including 0 and old items which comes to 16 000 Items
So my macro runs in loops with in loops which finds items and puts the value into the correct field. in 3 stages.

It works but takes long, atleast i can give a report for now.
I will try the outer join thing for a permanent solution. I some how think that it wont work cut i think i have tried it.
But the stock on hand inventory list wasnt showing 0 on hand items though.

SBSP, you should try and put all of that in SQL as a Stored Procedure. Then it will be 10x faster. As a rule I don't use any kind of scripting in Crystal except for formatting which Crystal is best for. SQL Server is much faster and more precise when it comes to doing calculations.

RuadRauFlessa wrote:SBSP, you should try and put all of that in SQL as a Stored Procedure. Then it will be 10x faster. As a rule I don't use any kind of scripting in Crystal except for formatting which Crystal is best for. SQL Server is much faster and more precise when it comes to doing calculations.

I'm thinking of creating 3 views then a final view combined from that.
Or like you say a storedproc.

But i'm not good with SQL stored procs.
The other thing is if i do this , This just add an extra shlep to the system.
Cus i know this report will become popular, (Very good for forecasting stock)

So when there is upgrades all this has to be upgraded or accounted for.

SBSP wrote:

RuadRauFlessa wrote:SBSP, you should try and put all of that in SQL as a Stored Procedure. Then it will be 10x faster. As a rule I don't use any kind of scripting in Crystal except for formatting which Crystal is best for. SQL Server is much faster and more precise when it comes to doing calculations.

I'm thinking of creating 3 views then a final view combined from that.
Or like you say a storedproc.

But i'm not good with SQL stored procs.
The other thing is if i do this , This just add an extra shlep to the system.
Cus i know this report will become popular, (Very good for forecasting stock)

So when there is upgrades all this has to be upgraded or accounted for.

All the more reason to use a single stored proc for the job. It will cut down on execution time and soforth. Also as a rule try and use nolock if you only draw data for reports.

SELECT * FROM [dbo].[TABLE1] WITH (NOLOCK)

The query will read over records with read/write locks. It will also not mark any records as read locked. The chance of getting a deadlock is thus null and void. You should, on a fairly busy system, see a notable performance difference.

Will a query lock a table if the logon user has read only rights ?

For me to explain when each type of locking occurs is a separate essay in itself.

SBSP wrote:Will a querry lock a table is the logon user has read only rights ?

Yes. Any query you pull has to lock the record it is busy with otherwise you might get concurrency issues. In the case of a nolock the record will read the last committed version of the record if the record is currently write locked. If you do a select without it then it will turn out that the write query trying to update the table waits until the select is finished and then only updates the record. It is so that you only read records which is correct. A nolock will not give you inaccurate results but could rather not contain the latest result as one of the selected records may have been written to while it executed. There is no risk in using it. There is another option which is the readover but that will cause the record to be omitted from the resultset as when it sees that the record is locked it will simply skip it.

RuadRauFlessa wrote:

SBSP wrote:Will a querry lock a table is the logon user has read only rights ?

Yes. Any query you pull has to lock the record it is busy with otherwise you might get concurrency issues. In the case of a nolock the record will read the last committed version of the record if the record is currently write locked. If you do a select without it then it will turn out that the write query trying to update the table waits until the select is finished and then only updates the record. It is so that you only read records which is correct. A nolock will not give you inaccurate results but could rather not contain the latest result as one of the selected records may have been written to while it executed. There is no risk in using it. There is another option which is the readover but that will cause the record to be omitted from the resultset as when it sees that the record is locked it will simply skip it.

Thanks ron2k and RuadRauFlessa.

Thought as much.

On a different note.

I can hardly read my own posts

I sometimes only type can when i meant "can't" and often type "is" when i meant if.
And to mix up those to words makes things confusing.