If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Query corresponding to date range.
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 |
#2
|
|||
|
|||
Query corresponding to date range.
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 |
#3
|
|||
|
|||
Query corresponding to date range.
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" wrote: 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 |
#4
|
|||
|
|||
Query corresponding to date range.
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 |
#5
|
|||
|
|||
Query corresponding to date range.
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" wrote: 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 |
#6
|
|||
|
|||
Query corresponding to date range.
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 |
#7
|
|||
|
|||
Query corresponding to date range.
|
#8
|
|||
|
|||
Query corresponding to date range.
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 |
#9
|
|||
|
|||
Query corresponding to date range.
ok thanks, but now I need some time to take the idea of this design.
Thanks again, "Lord Kelvan" wrote: 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 |
#10
|
|||
|
|||
Query corresponding to date range.
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" wrote: 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 |
|
Thread Tools | |
Display Modes | |
|
|