Adding to a query

[Briansmi1116]

I have a downloaded Access template I am trying to use. I am having a problem tying to add to the query. I am still learning so any help would be great. The SQL follows.

SELECT DISTINCTROW [Workorder Parts].WorkorderID, Sum([Quantity]*[UnitPrice]) AS [Parts Total]
FROM [Workorder Parts]
GROUP BY [Workorder Parts].WorkorderID;


I added a discount Field in a table, and a form to insert which parts I want to have the discount added to. Now I need to be able to subtract the percentage discount from the [Parts Total] Field and display it in the query. I then need to pull the query into the form where it shows the [Parts Total].

Thanks for your help in advance, and let me know if there is anything else you need.

Thanks,

Brian

[FishVal]

Hi, Brian.

Please include the MetaData for all relevant datasets. Here is an example of how to post table MetaData :
Table Name=tblStudent

[i]Field; Type; IndexInfo[/i]
StudentID; AutoNumber; PK
Family; String; FK
Name; String
University; String; FK
Mark; Numeric
LastAttendance; Date/Time

[Briansmi1116]

Thanks for replying.
OK, I apologize for not having that info, I try to get the info you need.

Table name is Workorder Parts

Feild Name; Feild Type;

WorkorderPartID ; AutoNumber;
WorkorderID; Number;
PartID; Number;
Quantity; Number;
Discount; Number;
UnitPrice; Currency;

I hope this helps.

Thanks,

Brian

[FishVal]

Well.

That seems to be simple.
[code=sql]
SELECT [Workorder Parts].WorkorderID, Sum([Quantity]*[UnitPrice]*(1-[Discount]/100)) AS [Parts Total]
FROM [Workorder Parts]
GROUP BY [Workorder Parts].WorkorderID;
[/code]

The only question is why do you use DISTINCTROW predicate. Does you table contain fully duplicate records?

[Briansmi1116]

Yes the Workorder Parts Table has duplicate PartID's for the diffrent WorkorderID's. I didn't choose the idea, I just downloaded the template, and now I am trying to modify it to my use. So would I still need the distinctrow?

[Briansmi1116]

What I need to do is find the total price of parts, multiply it by the discount percentage, and subtract it from the total price of the parts. Does that make sense?

[FishVal]

What I need to do is find the total price of parts, multiply it by the discount percentage, and subtract it from the total price of the parts. Does that make sense?

So far you have discount value for each product and it is supposed that discount is for entire order. Am I right?
If so, then your table design is not suited for that.
Read Database Normalisation and Table structures article to get ideas on it.
Just for starters: you should have [tblOrders] and [tblOrderProduct s] related one-to-many.

Yes the Workorder Parts Table has duplicate PartID's for the diffrent WorkorderID's. I didn't choose the idea, I just downloaded the template, and now I am trying to modify it to my use. So would I still need the distinctrow?

No. DISTINCTROW predicate allows to select from entirely unique records (eliminating records where all fields are the same). Read Access help on DISTINCT/DISTINCTROW predicates.

[Briansmi1116]

I think I found an alternate way. I have a form where it gives the amount due as a total. It gets information from three querys, and a table, for the form. I'm trying to get the discount percent added to this form, but I am unable to do so. I tried to change the control source to point at the feild, but it keeps showing "#Name?". What am i missing?

[Briansmi1116]

Never mind, I figured it out. Thanks again for your help.