Help buliding SQl Statment - comp.databases.ms-access

Sorry for the long post, I get the basics of SQL but when it get to joins, adding, summing etc I get= very lost very quickly, I just can=92t get my head round SQL. I will try = and explain as best I can what I am trying to do, I have a program I=92m wo= rking on that will hold a list of all my stock in an Access DB, I want to u= se this data to calculate quotes, bookings and show what stock is available= etc. Stock table structures are as follows. StockCategories (list of the Categories eg lighting, drapes, sound etc) ID ListOrder Category StockItemDescriptions ID CategoryID Description DailyRate ThrreDayRate Week= lyRate AssociatedID BoxQty StockItemDetail=20 ID DescriptionID IDNumber SerialNumber PATDate ItemAva= ilable I have another table that will be used to hold a list of items that have ei= ther been marked as Quoted or Booked, not sure if I have all the fields I= =92m going to need=20 StockAvailability ID EventID EventDate GetInDate GetOutDate Generation= Date DescriptionID ItemDescription QtyRequired ItemPrice = Notes Status The GetIn and GetOut dates will be used to work out what stock items are av= ailable for the dates I am doing the quote or booking for. The status will = either be =91Quoted=92 or =91Booked=92. GenerationDate the date I created t= he quote or booking, this is more for me to know which is the most recent So here is what I am trying to do and the 2 statements needed (the stuff th= at is way over my head) Statement 1 =96 Show Stock Return a dataset of all stock from the stock category. Run the same SQL sta= tement for each category, this will allow me to format the list of stock it= ems on the screen in groups with group headers, these headers will be in th= e order of ListOrder from StockCategories, this to show items on the screen= in a logical order, eg all the lights together, all the drapes etc,=20 So I will end up with the following. Category 1 Item 1 Item 2 Item 3 Category 2 Item 1 Item 2 Item 3 Item 4 And so on The returned dataset will show the description, hire rates, available stock= Qty for the date range (GetIn and GetOut). The QTY is base on how many are= available from the ItemAvailable (this field is True or False, there in c= ase an item is out of service) in the StockItemDetail table, and Qty Quoted= and Qty Booked from the StockAvailability (See below) The Associated items was just going to be a comma list of ID=92s from the S= tockItemDescriptions, however these could be held in another table if easie= r, will take advise on this. The returned dataset should be something like this, the Qty-Q is how many h= ave been quoted for in that date range, and Qty-B is how many are booked ou= t. The QTYs are based on that date range, so if there are items already boo= ked out or quoted within that range it takes them into account. The QTYs ar= e only there for a visual, there may only be 5 available but I can select 1= 0, this should then return -5 the next time I the statement.=20 ID Description DailyRate 3DayRate WeeklyRate Qty-A Qty-Q Qty-B Associat= ed items =20 23 300w light 10 15 30 4 2 0 24,56 45 400w light 12 17 32 8 4 0 01,56 48 Starcloth 100 150 200 8 5 3 35 4 Big Light 200 275 350 10 5 0 45,67,45,12 I will use this returned dataset to display it on the screen, select what I= need from the stock, then I will save it to the StockAvailability table, I= will only save the items selected (QtyRequired >1), not the full list of s= tock items.=20 If I need to load the quote to edit it, or convert it to a booking I will l= oad the full stock dataset again, load the dataset saved in the StockAvaila= bility table and then update my on screen list of QtyRequired by matching = the DescriptionID in the full dataset agonists the StockAvailability datase= t.=20 To convert a Quote to a Booking I will update Status in the StockAvailabili= ty from Quoted to Booked Statement 2 =96 Packing List This one should be easy, I will select a date (Event Date) and this will re= turn a list of all items to be packed for that date, this could be multiple= events 300w light 4 per box 400w light 4 per box Starcloth 2 per box Big light 2 per box Description Number Required Number of Boxes Clients name 300w Light 4 1 John Smith 400w Light 2 1 John Smith Ken Smith Starcloth 3 2 John Smith David Smith Big Light 4 2 John Smith Ken Smith Clients name comes from a Table called EventDetails that has a field called= ClientsName and one called EventDate Thanks in advance for any help Cheers Brian

On Sat, 11 Aug 2012 14:28:18 -0700 (PDT), "Brian ." <djswirl@yahoo.com> wrote: Think the first thing to do is get your table structure right. All the IDs are confusing Try instead StockCategories (list of the Categories eg lighting, drapes, sound etc) StockCategoriesID ListOrder Category StockItemDescriptions StockItemDescriptionsID StockCategoryID Description DailyRate ThrreDayRate WeeklyRate AssociatedID BoxQty StockItemDetailS StockItemDetailID StockDescriptionID IDNumber SerialNumber PATDate ItemAvailable You may want to shorten some of the names, personally I would use Categories StockDescs StockDetails 'cause I'm a lousy typist and try to keep things short but meaningful. Anyway everything is logical and points back to the correct table, Have you set up relationships and enforced referential integrety. This means that you can't have a StockItemDescription without first having a Stock Category, So you can't add a Lotus anless you have a category of Cars set up first. Now the availability table is tricky. I think there are 2 situations. One is where you hire out your Lotus and you will have the serial number, so that is linked to your Stock Item details table, the other situaltion is where you are hiring out lights. Are they against an individual item or against the more general StockItemDescriptions table. If it's the former, then no problem. You don't need the description - this is picked up from the StockItemsDescription table. In the StockItemDetailS table, I suspect that the ItemAvailable is calculated from the Availabilty table. If so it should not be stored. The first 3 tables should be pretty static, just changing when a new item is added or removed. Will come back when you sort out the queries Phil

Hi Phil Thanks for taking the time to reply. I have taken you advice in changing th= e IDs with the tables. I have left the table names as there are so many tal= es within this project with them starting with the word STOCK helps me work= out whats what. Yes there are relationships set up so you can not add a Stock description w= ithout a Stock category, and you can not add Stock details without a Stock = description. With regards to the availability all that happens is an item gets hires out= , it=92s doesn=92t matter which one so don=92t need to know details of the = individual item (serial number etc) So I have 10 lights I=92m hiring out 5 = of them grab 5 from the shelf. The StockAvailability tables is the one that will change with every quote o= r booking, and yes you are correct in saying that I don=92t need the descri= ption as I pick this up from the StockItemsDescription Table.=20 The Stock details is more for internal tacking of each item and I can put h= istory against them (may be added a few more fields later), should an item = break or be off for a service then I mark that one as unavailable and all t= his dose it take the over all availability down by 1 so number of available= lights now is 9 =20 You are also correct the first 3 tables will not change very much, unless s= tock is added or and item has been mark as unavailable or the hire price ch= anges. Regards Brian

On 12/08/2012 13:17:28, "Brian ." wrote: > Hi Phil > > Thanks for taking the time to reply. I have taken you advice in changing > th e IDs with the tables. I have left the table names as there are so many > tal es within this project with them starting with the word STOCK helps me > work out whats what. > > Yes there are relationships set up so you can not add a Stock description > w ithout a Stock category, and you can not add Stock details without a > Stock description. > > With regards to the availability all that happens is an item gets hires > out , it�s doesn�t matter which one so don�t need to know details of the > individual item (serial number etc) So I have 10 lights I�m hiring out 5 > of them grab 5 from the shelf. > > The StockAvailability tables is the one that will change with every quote > o r booking, and yes you are correct in saying that I don�t need the > descri ption as I pick this up from the StockItemsDescription Table. > > The Stock details is more for internal tacking of each item and I can put > h istory against them (may be added a few more fields later), should an > item break or be off for a service then I mark that one as unavailable and > all t his dose it take the over all availability down by 1 so number of > available lights now is 9 > You are also correct the first 3 tables will not change very much, unless > s tock is added or and item has been mark as unavailable or the hire price > ch anges. > > Regards > > Brian > > > So don't worry about SQL, just use the query builder. I Confess I've been using Assess from version 1.1 and rarely key in SQL directly. Even when using it with VBA, I use the Query Builder and copy the SQL into the VBA module. So for the first report, you need a query based on the StockCategories & StockItemDescriptions tables The report wil have a group header Based on Category, and the detail section will contain the Stock Items with whatever info you want, probably sorted by description. If you want to do it in form view, the method is completely different. Here you need a main form based on a query showing StockCategories and a subform based on a query showing StockItemDescriptions. The linking field will be StockCategoryID See how that works out first Phil

> So don't worry about SQL, just use the query builder. > > I Confess I've been using Assess from version 1.1 and rarely key in SQL > > directly. Even when using it with VBA, I use the Query Builder and copy the > > SQL into the VBA module. > > > > So for the first report, you need a query based on the > > StockCategories & StockItemDescriptions tables > > The report wil have a group header Based on Category, and the detail section > > will contain the Stock Items with whatever info you want, probably sorted by > > description. > > > > If you want to do it in form view, the method is completely different. > > Here you need a main form based on a query showing StockCategories and a > > subform based on a query showing StockItemDescriptions. The linking field > > will be StockCategoryID > > > > See how that works out first > > Phil Hi Phil I should have said that I am only using MS Access as the DB backend, all the queries are going to be run within a program written in Delphi. I have tried to us the Query Builder and got even less far then hand writing the SQL statements. Brian

Assuming you do have the Access product installed, you create a test or working database, link to the same tables, create a Query and then display its SQL (test if you wish... it's likely easier to test the Query from the Query Builder than it is to copy the SQL into the Delphi and then test the combination of the Delphi and the SQL)... finally copy the SQL, paste it into your Delphi code, and execute from there -- more testing will, most likely, be required at that point. -- Larry Linson Microsoft Office Access MVP Co-Author, Microsoft Access Small Business Solutions, Wiley 2010

Hi guys OK after a few days I trying to get the SQL to work using the query builder= I am not further along, I can not get it to link the table how I want. The query builder depend on your know what you need to link and how, as I s= aid from my first post I just don=92t get all the left and right and inner = joins etc so I am still stuck Regards Brian