Expression frustration and a form question

[Michael Plant]

Hi all.

I'm trying to write my first Access database and I'm getting on well. It's
for my own small business use and the scenario is this. I have 4 or 5
suppliers who all email their product pricing and databases daily. As they
all use different formats etc, I have linked all their data and import using
append queries into a single table in a format of my choice. I then
manipulate the data and use it to update my pricing according.

I have 2 problems.

1. Once the pricing has been updated, I want to produce a report showing me
profit margins as confirmation that I've updated my prices correctly. I'm
drawing data from 2 tables, one being my own price table ("tbl_SAGE") and
the other being the full list as above ("tbl_full_list"). Because my price
is based on the cheapest supplier who happens to have stock, I use a select
query with the following fields:
product_code; manu_part_no; description; cost_price; sale_price; stock
(I use manu_part_no as the link between tables, showing all in tbl_SAGE and
those in tbl_full_list that match)
All the fields draw from tbl_SAGE except for cost_price & stock which come
from tbl_full_list. cost_price uses MIN in the total field to find the
cheapest price. What I need to do is filter out all the "0" stock levels,
however because I'm using totals it seems to ignore the MIN and shows all
matching manu_part_no that <> "0".

What I'm looking to achieve is tbl_SAGE pulls from tbl_full_list the minimum
cost_price for the product that matches manu_part_no and the stock quantity
(ignoring zero stock). I've tried DLOOKUP as an expression but without a
form to draw from I've been stuck.

This is harder to describe than I thought! Bearing in mind I don't think
i've made myself clear, I'll skip question 2 for now else this'll be a long
post!

Would anyone have any advice?

Kind regards,
Michael

[Allen Browne]

This should give you what you need. It stacks one query on top of another.

1. Create a query into tbl_full_list price.

2. Depress the Total button on the toolbar (upper sigma icon).
Access adds a Total row to the grid.

3. Drag the ProductID into the output grid.
Accept "Group By" in the Total row.

4. Drag the price field into the grid.
Choose "Min" in the Total row.

5. Drag the quantity field into the grid.
Choose "Where" in the Total row.
In the Criteria for, enter >0

6. Save the query.

7. Create another query into your own price list (tbl_SAGE), your product
list, and the query above. You should have a join between product and the
other two only (i.e. no join between the query and tbl_SAGE).

8. Drag whatever fields you want into the output grid, and base the report
on that.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Michael Plant" <iu*****@hotmail.com> wrote in message
news:10****************@ersa.uk.clara.net...

I'm trying to write my first Access database and I'm getting on well. It's
for my own small business use and the scenario is this. I have 4 or 5
suppliers who all email their product pricing and databases daily. As they
all use different formats etc, I have linked all their data and import using append queries into a single table in a format of my choice. I then
manipulate the data and use it to update my pricing according.

I have 2 problems.

1. Once the pricing has been updated, I want to produce a report showing me profit margins as confirmation that I've updated my prices correctly. I'm
drawing data from 2 tables, one being my own price table ("tbl_SAGE") and
the other being the full list as above ("tbl_full_list"). Because my price
is based on the cheapest supplier who happens to have stock, I use a select query with the following fields:
product_code; manu_part_no; description; cost_price; sale_price; stock
(I use manu_part_no as the link between tables, showing all in tbl_SAGE and those in tbl_full_list that match)
All the fields draw from tbl_SAGE except for cost_price & stock which come
from tbl_full_list. cost_price uses MIN in the total field to find the
cheapest price. What I need to do is filter out all the "0" stock levels,
however because I'm using totals it seems to ignore the MIN and shows all
matching manu_part_no that <> "0".

What I'm looking to achieve is tbl_SAGE pulls from tbl_full_list the minimum cost_price for the product that matches manu_part_no and the stock quantity (ignoring zero stock). I've tried DLOOKUP as an expression but without a
form to draw from I've been stuck.

This is harder to describe than I thought! Bearing in mind I don't think
i've made myself clear, I'll skip question 2 for now else this'll be a long post!

Would anyone have any advice?

Kind regards,
Michael

[Michael Plant]

Allen

My 'thank you' was lost as I thought I'd sent to group but apparently not.
After posting another message I realised that I was doing it wrong (first
time using Outlook Express for news) so don't think I didn't appreciate your
reply!

Kind regards,
Michael Plant

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:40**********************@freenews.iinet.net.a u...

This should give you what you need. It stacks one query on top of another.

1. Create a query into tbl_full_list price.

2. Depress the Total button on the toolbar (upper sigma icon).
Access adds a Total row to the grid.

3. Drag the ProductID into the output grid.
Accept "Group By" in the Total row.

4. Drag the price field into the grid.
Choose "Min" in the Total row.

5. Drag the quantity field into the grid.
Choose "Where" in the Total row.
In the Criteria for, enter >0

6. Save the query.

7. Create another query into your own price list (tbl_SAGE), your product
list, and the query above. You should have a join between product and the
other two only (i.e. no join between the query and tbl_SAGE).

8. Drag whatever fields you want into the output grid, and base the report
on that.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Michael Plant" <iu*****@hotmail.com> wrote in message
news:10****************@ersa.uk.clara.net...

I'm trying to write my first Access database and I'm getting on well. It's for my own small business use and the scenario is this. I have 4 or 5
suppliers who all email their product pricing and databases daily. As they all use different formats etc, I have linked all their data and import

using

append queries into a single table in a format of my choice. I then
manipulate the data and use it to update my pricing according.

I have 2 problems.

1. Once the pricing has been updated, I want to produce a report showing

me

profit margins as confirmation that I've updated my prices correctly. I'm drawing data from 2 tables, one being my own price table ("tbl_SAGE") and the other being the full list as above ("tbl_full_list"). Because my price is based on the cheapest supplier who happens to have stock, I use a

select

query with the following fields:
product_code; manu_part_no; description; cost_price; sale_price; stock
(I use manu_part_no as the link between tables, showing all in tbl_SAGE

and

those in tbl_full_list that match)
All the fields draw from tbl_SAGE except for cost_price & stock which come from tbl_full_list. cost_price uses MIN in the total field to find the
cheapest price. What I need to do is filter out all the "0" stock levels, however because I'm using totals it seems to ignore the MIN and shows all matching manu_part_no that <> "0".

What I'm looking to achieve is tbl_SAGE pulls from tbl_full_list the

minimum

cost_price for the product that matches manu_part_no and the stock

quantity

(ignoring zero stock). I've tried DLOOKUP as an expression but without a
form to draw from I've been stuck.

This is harder to describe than I thought! Bearing in mind I don't think
i've made myself clear, I'll skip question 2 for now else this'll be a

long

post!

Would anyone have any advice?

Kind regards,
Michael