Query corresponding to date range.

[maella]

Hi and sorry by my grammar mistakes (I'm Spanish)
I've got a table [tblSales] with the following fields:
[id] [Name] [Datesf] [Quantity]
and I've got another table with the prices of this item (we suppose there is
an item only), that changes every one or two weeks:
[id] [DateStart] [DateEnd] [Price]
I would like to get a query of totals (sum) for the [Quantity] grouped for
each client and multiplied by its corresponding price depending on the date.

It would be easy for only one range of dates, but I would preffer to get all
the data together...

Thanks in advance

 

[Lord Kelvan]

i am presuming that there is only one item otherwise your table
structure cannot support as there is no item table

what would have been eaiser is to put the price in the sales table so
your table would be

tblsales
salesid
clientname
datesf
quantity
price

so at the time of sale you put the price there

regardless this query should do it

SELECT tblsales.name, Sum([quantity]*[price]) AS [total sales]
FROM tblsales,tblprices
where tblsales.datesf between datestart and dateend
GROUP BY tblsales.name;

Regards
Kelvan

 

[maella]

Hi Lord Kelvan:

Imagine a fruit cooperative (warehouse), each farmer enter several entries
of fruit every summer and there are several varities and farmers. Then a
machine select each entry, giving as a result 12 calibers of fruit. I have to
save the weight of each caliber, so my table is:
[entryid],[entrydate],[username],[variety],[16],[18],[20],[24],[26],...
Then, at the end of the autumn, when all the fruit are sold and charged, the
cooperativa has to pay to the farmers. Each kg of each caliber has got a
price depending on the variety and the entry data range. Usually the prices
change each one or two weesks. My table is:
[priceid],[variety],[startdate],[enddate],[16],[18],[20],[24],[26],[28],....

I only need to know how to link each entry with its corresponding price, for
that i simplified the approach of this thread.

I've started to work with Access two weeks ago, and the only thing left is
to do that.

Thanks

 

[Lord Kelvan]

well i think your table structure is completly wrong no offence
databasing isnt something you can learn over night

what are the numbers in your table

i think you are going to have to rebuild your table so that it will
work for what you want to do

i can help you by telling you the different tables and where the
fields go and how they work

for me to do this i need you to list all your field and a description
of each field ie what it stores and also the datatype of each field ie
number text etc and ill try to help you build a propper DB

Regards
Kelvan

 

[maella]

Hi, don't worry, advices always are wellcome for me.
I'm willing to do all you say me. I solved the problem whe the range of
dates are two weeks creating a compound code (number) like 20081011000 where
the first four digits are the year, the following two are the month, the f.
one are the fortnight (1 or 2) and the last four digits are the variety code.
That gets I can calculate the amount depending on the prices, corresponding
to two weeks. Now, I'll include another fruits with the prices changing every
3 or 4 days, and that won't work, so I'll look forward your answer.

I've thought this data base for the farmers, but in each home can be several
ones (for example the man and his wife or their children).
When a farmer makes an entry in the wharehouse (well, after passing the
fruit in a selection machine), a receipt is given by the cooperative. It
includes:

Entry code: numerical, wharehouse code (to me only information function)

Date: date, entry date

User: number, farmer name. I have a combo showing the variety name (string)
but saving the userId (number)

Variety: number, fruit variety. I have a combo showing the variety name
(string)
but saving the varietyId (number)

16: number, weight of the fruit with 16 caliber.

18: number, weight of the fruit with 18 caliber.

20: number, weight of the fruit with 20 caliber.
.............

juice: number, weight of the fruit with "juice" caliber (damaged)

****************************************************

There are 12 calibers in all. My entry table has all this fields (16 in all).
Then I've got another table with the prices with the following fields:

variety: number, number, fruit variety. I have a combo showing the variety
name
(string) but saving the varietyId (number). Each variety has
different prices
in each caliber.

startdate: date, the first date valid for these prices

enddate: date, the last date valid for these prices so these are valid in
the date
date range.

price16: currency, price of each kg of fruit with 16 caliber

price18: currency, price of each kg of fruit with 18 caliber

price20: currency, price of each kg of fruit with 18 caliber

.................

pricejuice: corrency, price of each kg of fruit with "juice" caliber


*******************************************************

I have all these fields in my prices table too, 16 if I add an Id.

I hope I've explained well.

Thanks for your attention

 

[Lord Kelvan]

ok you need a series of tables

tblfarmer
farmerid,autonumber,id field for farmer
farmername, text, name of farmer
otherfarmerdetails

tblvariety
varietyid,autonumber,id field for fruit
varietyname,text,name of fruit
othervarietydetails

tblcaliber
caliberid,autonumber,id field for caliber
calibersize,number,size of caliber ie 16 or 18
othercaliberdetails

tblprice
priceid,autonumber,id field for price
caliberid,number,fielding connecting calaber to price
pricestartdate,date/time,start date of price
priceenddate,date/time,end dat of price
priceamount,currency,value of price per KG
varietyid,number,id linking fruit to price

tblwarehouse
warehouseid,autonumber,id field for warehouse
farmerid,number,field connecting farmer to entry in warehouse
varietyid,number,field connecting fruit to entry in warehouse
caliberid,number,field connecting calaber to entry in warehouse
warehouseentrydate,date/time,date of entry in warehouse
entryquantity,number,weight of fruit of this calaber in warehouse
entrypaid,yes/no,money has been paid for the entry

the entry paid field may not be needed and you could just sue two date
ranged from begining fo autumn to end of autumn i think it is better
to do date ranges rather than using that field.

so in this example tblwarehouse is the most important table

it would store
warehouseid farmerid varietyid,calaberid,warehouseenrtydate
1 1 1 1
01/01/2008
2 1 3 2
01/01/2008
etc

from this we can get the information you want

then this query joins it all up for you

SELECT tblfarmer.farmername, tblvariety.varietyname,
tblcaliber.calibersize, tblwarehouse.warehouseentrydate,
tblwarehouse.entryquantity, (select subprice.priceamount from tblprice
as subprice where tblwarehouse.varietyid = subprice.varietyid and
tblwarehouse.caliberid = subprice.caliberid and
tblwarehouse.warehouseentrydate between subprice.pricestartdate and
subprice.priceenddate) AS priceamount
FROM tblcaliber INNER JOIN (tblvariety INNER JOIN (tblfarmer INNER
JOIN tblwarehouse ON tblfarmer.farmerid = tblwarehouse.farmerid) ON
tblvariety.varietyid = tblwarehouse.varietyid) ON tblcaliber.caliberid
= tblwarehouse.caliberid;

so you would see something like

farmername,varietyname, calibersize, warehouseentrydate,
entryquantity, priceamount
bob,apples,16,1/01/2008, 5, $5.00
bob,apples,16,1/01/2008,6,$5.00
bob,apples,16,4/01/2008,3,$6.00
frank,apples,16,1/01/2008,10,$5.00

this is what you are looking for then you can do a simple query

SELECT [get warehouse info].farmername,
Sum([entryquantity]*[priceamount]) AS [amount owed]
FROM [get warehouse info]
WHERE ((([get warehouse info].warehouseentrydate) Between [enter start
date] And [enter end date]))
GROUP BY [get warehouse info].farmername;

to get the total amount owed to each farmer

if you want a copy of what i was working on ie the db file send me
your email and ill send you a copy of it unless you think you can
build it yourself

hope this helps

Regards
Kelvan

 

[maella]

ohh thanks Lord Kelvan, but I wouldn't take your time... I must do it by
myself.
Anyways this is my mail: (e-mail address removed)

ok you need a series of tables

tblfarmer
farmerid,autonumber,id field for farmer
farmername, text, name of farmer
otherfarmerdetails

tblvariety
varietyid,autonumber,id field for fruit
varietyname,text,name of fruit
othervarietydetails

tblcaliber
caliberid,autonumber,id field for caliber
calibersize,number,size of caliber ie 16 or 18
othercaliberdetails

tblprice
priceid,autonumber,id field for price
caliberid,number,fielding connecting calaber to price
pricestartdate,date/time,start date of price
priceenddate,date/time,end dat of price
priceamount,currency,value of price per KG
varietyid,number,id linking fruit to price

tblwarehouse
warehouseid,autonumber,id field for warehouse
farmerid,number,field connecting farmer to entry in warehouse
varietyid,number,field connecting fruit to entry in warehouse
caliberid,number,field connecting calaber to entry in warehouse
warehouseentrydate,date/time,date of entry in warehouse
entryquantity,number,weight of fruit of this calaber in warehouse
entrypaid,yes/no,money has been paid for the entry

the entry paid field may not be needed and you could just sue two date
ranged from begining fo autumn to end of autumn i think it is better
to do date ranges rather than using that field.

so in this example tblwarehouse is the most important table

it would store
warehouseid farmerid varietyid,calaberid,warehouseenrtydate
1 1 1 1
01/01/2008
2 1 3 2
01/01/2008
etc

from this we can get the information you want

then this query joins it all up for you

SELECT tblfarmer.farmername, tblvariety.varietyname,
tblcaliber.calibersize, tblwarehouse.warehouseentrydate,
tblwarehouse.entryquantity, (select subprice.priceamount from tblprice
as subprice where tblwarehouse.varietyid = subprice.varietyid and
tblwarehouse.caliberid = subprice.caliberid and
tblwarehouse.warehouseentrydate between subprice.pricestartdate and
subprice.priceenddate) AS priceamount
FROM tblcaliber INNER JOIN (tblvariety INNER JOIN (tblfarmer INNER
JOIN tblwarehouse ON tblfarmer.farmerid = tblwarehouse.farmerid) ON
tblvariety.varietyid = tblwarehouse.varietyid) ON tblcaliber.caliberid
= tblwarehouse.caliberid;

so you would see something like

farmername,varietyname, calibersize, warehouseentrydate,
entryquantity, priceamount
bob,apples,16,1/01/2008, 5, $5.00
bob,apples,16,1/01/2008,6,$5.00
bob,apples,16,4/01/2008,3,$6.00
frank,apples,16,1/01/2008,10,$5.00

this is what you are looking for then you can do a simple query

SELECT [get warehouse info].farmername,
Sum([entryquantity]*[priceamount]) AS [amount owed]
FROM [get warehouse info]
WHERE ((([get warehouse info].warehouseentrydate) Between [enter start
date] And [enter end date]))
GROUP BY [get warehouse info].farmername;

to get the total amount owed to each farmer

if you want a copy of what i was working on ie the db file send me
your email and ill send you a copy of it unless you think you can
build it yourself

hope this helps

Regards
Kelvan

 

[Lord Kelvan]

just remember the key to a good db is how it is built for if the
foundations of a house are poor the house will fall over.

Regards
Kelvan

 

[maella]

ok thanks, but now I need some time to take the idea of this design.

Thanks again,

 

[maella]

Hi again,

Every time I can, I look through the db you sent me. It's works perfect and
is very simple. I think the main tool of the query is the sentence:

priceamount: (select subprice.priceamount from tblprice as subprice where
tblwarehouse.varietyid = subprice.varietyid and tblwarehouse.caliberid =
subprice.caliberid and tblwarehouse.warehouseentrydate between
subprice.pricestartdate and subprice.priceenddate

but I can't understand what is "subprice.priceamount" and the others
"subprice.*". This sentence is able to choose the correct price for each
caliber depend on the date, and I don't know how it does.

Besides I still think (IMHO) will be better to make a register for each
entry with the 12 caliber, will be more visual, since in each entry always
there are, at least, 8 or more of those calibers.

Thanks

 

[Lord Kelvan]

the subprice is a sub query based on the inital query to compare the
information and get the right value. dont worry too much about it
just dont change it. if you want to know more do some research on sub
queries

Regards
Kelvan