Help Me Fix This Querry

A

Aamer

am trying to create a querry to use in making an invoice



following is the querry in SQL view


SELECT Challan.[Challan #], Challan.[Trans ID], [Sales Org Desc With
Modified Descrip].CompanyName, [Sales Org Desc With Modified
Descrip].[Transaction Description], [Sales Org Desc With Modified
Descrip].[Modified Item Description], [Sales Org Desc With Modified
Descrip].Price, [Sales Org Desc With Modified Descrip].Odered, [Sales Org
Desc With Modified Descrip].Units, ([Price]*[Odered])-[Discount] AS Total,
[Sales Org Desc With Modified Descrip].Seller, Company.mobile, [Sales Org
Desc With Modified Descrip].[Struck#], [Sales Org Desc With Modified
Descrip].[Entry Type], [Sales Org Desc With Modified Descrip].[Sales Type]
FROM Company INNER JOIN (Challan INNER JOIN [Sales Org Desc With Modified
Descrip] ON Challan.[Trans ID] = [Sales Org Desc With Modified
Descrip].TransId) ON Company.CoId = [Sales Org Desc With Modified
Descrip].CoId;


the problem am facing is if by mistake a transaction is added in the querry
whose COMPANY NAME differs from another transaction that is eliminated in the
invoice.

This is not what i want, i want all the transactions in that perticular
invoice to dissappear instead.

Can Someone Please Help me fix this problem.

Thanks in advance
 
S

smartin

Aamer said:
am trying to create a querry to use in making an invoice



following is the querry in SQL view


SELECT Challan.[Challan #], Challan.[Trans ID], [Sales Org Desc With
Modified Descrip].CompanyName, [Sales Org Desc With Modified
Descrip].[Transaction Description], [Sales Org Desc With Modified
Descrip].[Modified Item Description], [Sales Org Desc With Modified
Descrip].Price, [Sales Org Desc With Modified Descrip].Odered, [Sales Org
Desc With Modified Descrip].Units, ([Price]*[Odered])-[Discount] AS Total,
[Sales Org Desc With Modified Descrip].Seller, Company.mobile, [Sales Org
Desc With Modified Descrip].[Struck#], [Sales Org Desc With Modified
Descrip].[Entry Type], [Sales Org Desc With Modified Descrip].[Sales Type]
FROM Company INNER JOIN (Challan INNER JOIN [Sales Org Desc With Modified
Descrip] ON Challan.[Trans ID] = [Sales Org Desc With Modified
Descrip].TransId) ON Company.CoId = [Sales Org Desc With Modified
Descrip].CoId;


the problem am facing is if by mistake a transaction is added in the querry
whose COMPANY NAME differs from another transaction that is eliminated in the
invoice.

This is not what i want, i want all the transactions in that perticular
invoice to dissappear instead.

Can Someone Please Help me fix this problem.

Thanks in advance
Click to expand...

Is COMPANY NAME actually stored in the invoice detail table?
 
A

Aamer

No COMPANY NAME is stored in COMPANY TABLE on the form "CHALLAN" when i
select the name of company from combo box it picks it from COMPANY TABLE and
stores in CHALLAN DETAIL TABLE along with other detail.

smartin said:
Aamer said:
am trying to create a querry to use in making an invoice



following is the querry in SQL view


SELECT Challan.[Challan #], Challan.[Trans ID], [Sales Org Desc With
Modified Descrip].CompanyName, [Sales Org Desc With Modified
Descrip].[Transaction Description], [Sales Org Desc With Modified
Descrip].[Modified Item Description], [Sales Org Desc With Modified
Descrip].Price, [Sales Org Desc With Modified Descrip].Odered, [Sales Org
Desc With Modified Descrip].Units, ([Price]*[Odered])-[Discount] AS Total,
[Sales Org Desc With Modified Descrip].Seller, Company.mobile, [Sales Org
Desc With Modified Descrip].[Struck#], [Sales Org Desc With Modified
Descrip].[Entry Type], [Sales Org Desc With Modified Descrip].[Sales Type]
FROM Company INNER JOIN (Challan INNER JOIN [Sales Org Desc With Modified
Descrip] ON Challan.[Trans ID] = [Sales Org Desc With Modified
Descrip].TransId) ON Company.CoId = [Sales Org Desc With Modified
Descrip].CoId;


the problem am facing is if by mistake a transaction is added in the querry
whose COMPANY NAME differs from another transaction that is eliminated in the
invoice.

This is not what i want, i want all the transactions in that perticular
invoice to dissappear instead.

Can Someone Please Help me fix this problem.

Thanks in advance
Click to expand...

Is COMPANY NAME actually stored in the invoice detail table?
Click to expand...
 
S

smartin

Hi Aamer,

Please be consistent with your descriptions.

Your query references three tables:
Company
Challan
[Sales Org Desc With Modified Descrip]

Which of these (if any) is
"CHALLAN DETAIL TABLE"

And which of these (if any) is
"invoice detail table"

I am betting you are storing COMPANY NAME in the invoice detail table.
This can lead to the problematic results if the wrong COMPANY NAME is
inserted into the detail.

The best solution is to normalize your tables.

http://en.wikipedia.org/wiki/Database_normalization

Work-arounds are possible, but you will be chasing after the solution
until you fix the root cause.

No COMPANY NAME is stored in COMPANY TABLE on the form "CHALLAN" when i
select the name of company from combo box it picks it from COMPANY TABLE and
stores in CHALLAN DETAIL TABLE along with other detail.

smartin said:
Aamer said:
am trying to create a querry to use in making an invoice



following is the querry in SQL view


SELECT Challan.[Challan #], Challan.[Trans ID], [Sales Org Desc With
Modified Descrip].CompanyName, [Sales Org Desc With Modified
Descrip].[Transaction Description], [Sales Org Desc With Modified
Descrip].[Modified Item Description], [Sales Org Desc With Modified
Descrip].Price, [Sales Org Desc With Modified Descrip].Odered, [Sales Org
Desc With Modified Descrip].Units, ([Price]*[Odered])-[Discount] AS Total,
[Sales Org Desc With Modified Descrip].Seller, Company.mobile, [Sales Org
Desc With Modified Descrip].[Struck#], [Sales Org Desc With Modified
Descrip].[Entry Type], [Sales Org Desc With Modified Descrip].[Sales Type]
FROM Company INNER JOIN (Challan INNER JOIN [Sales Org Desc With Modified
Descrip] ON Challan.[Trans ID] = [Sales Org Desc With Modified
Descrip].TransId) ON Company.CoId = [Sales Org Desc With Modified
Descrip].CoId;


the problem am facing is if by mistake a transaction is added in the querry
whose COMPANY NAME differs from another transaction that is eliminated in the
invoice.

This is not what i want, i want all the transactions in that perticular
invoice to dissappear instead.

Can Someone Please Help me fix this problem.

Thanks in advance
Click to expand...
Is COMPANY NAME actually stored in the invoice detail table?
Click to expand...
Click to expand...
 

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

Similar Threads

Please Help Me Fix This Querry 0
Can Someone Help Me Fix My Querry 1
Help needed fixing SQL querry 0
Help Me Fix Min Max Avg Querry 1
Invoicing problem 0

Top