record in a query (1 Viewer)

alain.roger · Mar 29, 2015

Hi,

i'm trying to refer to previous record in a query and i'm stuck as i do not know how to do it.

Here is my query:

Code:

SELECT 
 [Prod2Buy].[Product Code],
 IIF([Missing] IS NULL,0,[Prod2Buy].[Quantity To Order]-[To Order]) AS [Rest To Order],
 s.s_manu_code AS [Manufacturer Code],
 s.prod_cost AS [Price Code],
 IIF(s.supp_stock IS NULL,[Rest To Order],IIF(s.supp_stock<=[Rest To Order],s.supp_stock,"")) AS [To Order],
 ([Prod2Buy].[Quantity To Order]-s.supp_stock) AS [Missing]
FROM
 [SM1 Query] AS [Prod2Buy],
 (SELECT s.s_prod_code,s.s_manu_code, s.prod_cost, s.supp_stock FROM supplier_prod AS s,[SM1 Query] AS [Prod2Buy] WHERE s.s_prod_code=[Prod2Buy].[Product Code] ORDER BY prod_cost ASC)
WHERE
 Prod2Buy.[Promotional Product]="Yes"
AND
 [Prod2Buy].[Product Code]=s.s_prod_code
ORDER BY
 s.prod_cost ASC

in fact the line: IIF([Missing] IS NULL,0,[Prod2Buy].[Quantity To Order]-[To Order]) AS [Rest To Order]
should refer to previous record but i do not know how to cope with it

any help would be great.

Thx

llkhoutx · Mar 29, 2015

"Previous record" is not an Access word of art, that is, it has no specific meaning.

Sine you query is ordered by "s.prod_cost," what do you mean by "previous record." Assuming "s.prod_cost" is a cost, multiple records with the same value could have the same value and precede the subject order.

It is not clear what you want.

alain.roger · Mar 30, 2015

this select can return several matching records.
SELECT s.s_prod_code,s.s_manu_code, s.prod_cost, s.supp_stock FROM supplier_prod AS s,[SM1 Query] AS [Prod2Buy] WHERE s.s_prod_code=[Prod2Buy].[Product Code] ORDER BY prod_cost ASC)

and in this case, the master select can have several records when [Missing] is not equal to zero

plog · Mar 30, 2015

Can you demonstrate what you want with data? Include 2 sets:

A. What you are starting with. Sample data from all relevant tables, including enough records to cover all cases.

B. What you expect out of your query when you feed it A.

Include table and field names.

alain.roger · Mar 31, 2015

Hi Plog,

You have attached file with the whole documentation and database.
i'm trying to complete part B) question SM3 according to business rules described in part B)

If you have any question just let me know.
thx.

plog · Mar 31, 2015

Yes, I have a question:

Can you demonstrate what you want with data? Include 2 sets:

A. What you are starting with. Sample data from all relevant tables, including enough records to cover all cases.

B. What you expect out of your query when you feed it A.

Include table and field names.

alain.roger · Mar 31, 2015

Well, this is clearly written in the PDF in the ZIP file.
Data are in the database and the result of product we have to order returns the data in screenshot Query SM1.

and then we should get for SM3 what i partially got (see both query SM3 screenshots)
except that for NTB0001 we should have only 1 row.
All should should the correct "missing" values and "to order" values

plog · Mar 31, 2015

Well, this is clearly written in the PDF in the ZIP file.

No, no it is not--3 pages of text with only 1 table of data and it wasn't relevant. Also, I have no idea what all the screenshots in your last post represent. Which one(s) is the starting data and which one(s) is the ending data?

I don't know how to make it any clearer. I need 2 sets of data--starting and expected results. Think of me as Google maps--I can route a trip for you, but I need the orgination and destination. You can't just type "beach" into google maps and expect it to tell you how to get to from where you are to where you want to go. It (and I) need a starting point and a destination.

alain.roger · Apr 1, 2015

The access database includes 9 tables.

i'm trying to complete part B, question SM3 and PO... so question 3rd and 4th of part B.

Question SM1 ( query SM1.png ) of part B, allows me to know what product must be ordered and how much (so the starting point), but it does not tell:
- Who is the cheapest manufacturer
- if the cheapest manufacturer has not enough products in stock, we must find the next cheapest one and order to products left/missing.

For that we have the business rules describe in part B.
Result of question SM3 is the result called "query SM3 partial.png" and "query SM3 partial2.png" the problem is that i'm not able to make it work together.

i tried to use intermediate query to get the result of query SM3 partial2.png, where i can see who is the cheapest manufacturer for a certain product, how mush he has in stock(so how much i can order from him), but once i find the next one cheapest i'm not able to take result of previous record (so to know how much left unordered) and to use it to fill in "to Order" field.

Is it clearer ?

plog · Apr 1, 2015

Is it clearer ?

Nope. Good luck.

record in a query (1 Viewer)

alain.roger

Registered User.

llkhoutx

Registered User.

alain.roger

Registered User.

plog

Banishment Pending

alain.roger

Registered User.

Attachments

plog

Banishment Pending

alain.roger

Registered User.

Attachments

plog

Banishment Pending

alain.roger

Registered User.

plog

Banishment Pending

Similar threads

Users who are viewing this thread