Flexibility without verbosity
Astley Le Jasper
explain this without an example.
product
.id
.description
product_data
.product_id
.country_id
.price
.lots_of_other_attributes
Lets assume that I have a product that is sold throughout Europe. In 9
out of 10 countries the price is the same, but in one country the
price is slightly different. If I use above structure, I get the
flexibility to account for such differences, but it now means that I
have to enter 10 prices, when actually it could be only 2 - the
default and the exception.
Is there a clever and simple way to do this that I am missing?
Cheers
ALJ
.
Roger
wrote:
add a column called standardPrice in table product
and use it, unless an exception is found in table product_data
product
.id
.description
.stdPrice
product_data
.product_id
.country_id
.price
.lots_of_other_attributes
or create a product_data record with no country_id that can be used as
the standard price
Keith Wilby
news:f6b4481f-2073-4885...@m16g2000yqc.googlegroups.com...
How about "tblPriceLookup" linked to "product_data" in a 1:M relationship on
a new field PriceID?
Keith.
www.keithwilby.co.uk
paii, Ron
"Astley Le Jasper" <astley....@gmail.com> wrote in message
news:f6b4481f-2073-4885...@m16g2000yqc.googlegroups.com...
Try "Default Value" of the control on the form.
Astley Le Jasper
The actual database is quite a lot more complicated and I have to deal
with non-normalised data. Some of the products have attributes that
the others don't. Long story. So I'll have ...
product_data
.product_id
.country_id
.attribute_type
.attribute_value
Given this, the suggestion of having an empty country_id might be a
good one. I'll have a play with them and see where we get to.
Cheers
alj
Salad
How would it know the default? In the first record for a product, it'd
be 0. After that, you could get the value with the highest
count....which could still be incorrect.
Create a query. We'll pretend it's Query1
SELECT ProdID, Price, Count(Price) AS CountOfPrice
FROM YourTableName
GROUP BY ProdID, PRice
ORDER BY Count(Price) DESC;
Then in the AfterUpdate event for product code
If me.newrecord then
me.Price.DefaultValue = NZ(dlookup("Price","Query1","ProdID = " &
ME.ProdID),0)
Endif
Hmmm. You need to know the product ID before you can calc a default
value so maybe by the time you get to that point the default value is
already calced so maybe .DefaultValue is removed and it's just
me.Price = ...
Astley Le Jasper
SELECT product_id, country_id, price
FROM product_data
WHERE
Astley Le Jasper
I'm assuming the defaults are those that have no country id. What I've
come up with so far is filtering the product_data:
SELECT product_id, country_id, price
FROM product_data
WHERE country_id=2 OR country_id IS NULL
So this gives me the defaults and the over-rides. All I should need to
do is then remove the defaults where over-rides exist. So delete
records where the count of product_id >1 and country_id is null.
... haven't worked that out yet.
Salad
I don't fully understand your situation. Let's say you had ProductA for
apples. 5 countries charge .25, 3 of them .30, and 2 were .35. So what
is the default? Since the majority are .25, you could select the .25
but you'd have to group by on the product id and price and sort on the
count. I don't think country_id would help you out as you'd get 1
record for each country id. And if you had the count, and sorted it
DESCending, then the one with the most would float to the top.
Now you have be selling apples, bananas, and coffee. So I think you'd
need to select the product first before you could supply a default value
so I think you need to include to your where clause the product id
that matches your product id. If somebody went to the price field
before selecting a product, you'd have to trap for that as well.
You could even present a dropdown that displays the values, most to
least, and let the person override the values as well.
Like I said, I don't know your situation so I may be completely off base
here.
Astley Le Jasper
I don't have a form. I'm just using access as a backend for the
holding data. There is no user entry of the prices. I'll be entering
the data.
Let me give you another example.
[product_data]
product_id, country_id, price
Apples, None, €10
Apples, Germany, €9
Pears, None, €11
Pears, France, €12.90
Bananas, Germany, €3
Bananas, None, €4
Peaches, None, €5
...
In this case I filter the product_data by the country of interest or
where country is none, giving:
Apples, None, €10
Apples, Germany, €9
Pears, None, €11
Bananas, Germany, €3
Bananas, None, €4
Peaches, None, €5
Then I will need to get rid of the duplicate products where country_id
is None:
Apples, Germany, €9
Pears, None, €11
Bananas, Germany, €3
Peaches, None, €5
This gives me the prices for Germany including the country specific
data and the defaults. It means that each time I need the prices for a
country, I'll need to create a temp table. But this isn't a problem as
the database runs batch calculations each day, so performance isn't an
issue.
Do you know the best way off getting rid of duplicates?
Roger
wrote:
you shouldn't need to remove duplicates
create qryBasePrice as
SELECT product_id, price
FROM product_data
WHERE country_id IS NULL
create qryCountryPrice as
SELECT product_id, price, country_id
FROM product_data
WHERE country_id IS NOT NULL
this will give you the price of all products for a country
SELECT a.product_id
, nz(b.price, a.price) as product_price
FROM qryBasePrice AS a LEFT JOIN
qryCountryPrice as b on a.productId = b.productId
WHERE country_id = 2
Astley Le Jasper
working. Great. It didn't occur to me to use NZ().
Thanks everyone for all your suggestions.
SELECT
a.product_id, nz(b.price,a.price) AS price
FROM
qry_baseprice AS a
LEFT JOIN
(SELECT * FROM qry_countryprice WHERE country_id=2) AS b
ON a.product_id = b.product_id;
Salad
> Hi Salad,
>
> I don't have a form. I'm just using access as a backend for the
> holding data. There is no user entry of the prices. I'll be entering
> the data.
>
> Let me give you another example.
>
> [product_data]
> product_id, country_id, price
> Apples, None, �10
> Apples, Germany, �9
> Pears, None, �11
> Pears, France, �12.90
> Bananas, Germany, �3
> Bananas, None, �4
> Peaches, None, �5
> ...
>
> In this case I filter the product_data by the country of interest or
> where country is none, giving:
>
> Apples, None, �10
> Apples, Germany, �9
> Pears, None, �11
> Bananas, Germany, �3
> Bananas, None, �4
> Peaches, None, �5
>
> Then I will need to get rid of the duplicate products where country_id
> is None:
> Apples, Germany, �9
> Pears, None, �11
> Bananas, Germany, �3
> Peaches, None, �5
>
> This gives me the prices for Germany including the country specific
> data and the defaults. It means that each time I need the prices for a
> country, I'll need to create a temp table. But this isn't a problem as
> the database runs batch calculations each day, so performance isn't an
> issue.
>
> Do you know the best way off getting rid of duplicates?
I am not sure why you need to use a temp table. I would think that a
Totals query would do the trick. Drag your fields down, select
View/Totals from the menu or click the inverted M on the toolbar.
Assumes this is for a combo and the form name is form1 and there is a
combo called Combo1
Query1
SELECT Table1.Product, Table1.CountryCode, Table1.Price
FROM Table1
GROUP BY Table1.Product, Table1.CountryCode, Table1.Price
ORDER BY Table1.Product;
If you don't need the country code and you have selected the country
code do a
Me.Combo1.Requery
in the afterupdate event of selecting the country.
'this query doesn't show the country code
SELECT Table1.Product Table1.Price
FROM Table1
GROUP BY Table1.Product, Table1.CountryCode, Table1.Price
HAVING (Table1.CountryCode Is Null)) OR (Table1.CountryCode=
[Forms]![Form1]![CountryCode])
ORDER BY Table1.Product;
And if you need a country code. use Max.
Now you can select the records from Query1 that have a blank country
code or code that matches the country you are looking for.
'this query shows the country code. If there are 2 records, one with a
country code that matches the country and another that is blank, it will
show the value that has a country code
SELECT Table1.Product, Max(Table1.CountryCode) AS CC,
First(Table1.Price) AS DefPrice
FROM Table1
GROUP BY Table1.Product
HAVING (((Max(Table1.CountryCode)) Is Null Or
(Max(Table1.CountryCode))="USA"))
ORDER BY Table1.Product;