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
2  1356 
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*****@hotmai l.com> wrote in message
news:10******** ********@ersa.u k.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
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*********@Se eSig.Invalid> wrote in message
news:40******** **************@ freenews.iinet. net.au... 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*****@hotmai l.com> wrote in message
news:10******** ********@ersa.u k.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
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: Grant Hammond |
last post by:
I assume I'm not alone in my frustration that the expression builder
that comes (in part) with Access XP that dosnt wrap text when you open
it on an exisitng expression in a query or form.
I's bad enough that Microsoft dropped the expression builder from the
VB window (which I have restored using Michael Kaplan addin), but I
cant understand how they could not have fixed this obviously and
annoying BUG!@
|
by: Davy |
last post by:
Hi all,
I am a C/C++/Perl user and want to switch to Python (I found Python is
more similar to C).
Does Python support robust regular expression like Perl?
And Python and Perl's File content manipulation, which is better?
Any suggestions will be appreciated!
|
by: Mike |
last post by:
I have a regular expression (^(.+)(?=\s*).*\1 ) that results in
matches. I would like to get what the actual regular expression is.
In other words, when I apply ^(.+)(?=\s*).*\1 to " HEART (CONDUCTION
DEFECT) 37.33/2 HEART (CONDUCTION DEFECT) WITH
CATHETER 37.34/2 " the expression is "HEART (CONDUCTION DEFECT)". How
do I gain access to the expression (not the matches) at runtime?
Thanks,
Mike
|
by: anetasalomonsen |
last post by:
I am using xpath within a BizTalk 2006 orchestration.
I get the following error "Expression must evaluate to a node-set".
My xml:
<EmailListResponse xmlns="http://BTTest">
<PersonContact EMailContactValue="test1@mail.com" />
<PersonContact EMailContactValue="test2@mail.com" />
</EmailListResponse>
|
by: Earl |
last post by:
I have somewhat of an interesting scenario:
The form allows the user to select a service, which populates a a grid of
product information related to that service ("service grid"). The user can
then select from the products in the first grid and those choices populate
the second "choices grid".
The product grid is bound to a strongly-typed dataset table which can be
filled in a couple of different ways. More on this shortly.
| | |
by: Marc Gravell |
last post by:
In Linq, you can apparently get a meaningful body from and
expression's .ToString(); random question - does anybody know if linq
also includes a parser? It just seemed it might be a handy way to
write a safe but easy implementation (i.e. no codedom) for an
IBindingListView.Filter (by compiling to a Predicate<T>).
Anybody know if this is possible at all?
Marc
|
by: Nicodemas |
last post by:
Hello all, could not find a regular expression forum, so I thought I would post it to my favorite of the forums.
I have a series of applications I've developed which all use a centralized function that creates a connection object and executes an SQL query, i.e. function Query().
I always sanitize my SQL statements to buffer all apostrophes with two apostrophes ala function Buffer().
However, I have long wondered if I could do away with...
|
by: dspfun |
last post by:
Hi!
The words "expression" and "statement" are often used in C99 and C-
textbooks, however, I am not sure of the clear defintion of these
words with respect to C.
Can somebody provide a sharp defintion of "expression" and
"statement"? What is the difference between an expression and a
statement?
|
by: Shawn B. |
last post by:
Greetings,
I'm using a custom WebBrowser control:
http://www.codeproject.com/KB/miscctrl/csEXWB.aspx
When I get the DocumentSource of a web page I browsed, and run a regular
expression against it, the Expression never matches anything, nothing,
nadda. Never. I know it is a correct Regular Expression because if I use
the intrinsic WebBrowser control, it the expression works. I know that if I
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| | |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| | |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
