![]() I could have set this database up in that way, but I think the way I did it is probably better in this specific case. This is just a possible structure which will vary according to the needs I may have, but what I'll be looking out for is that, if I ever need to add a new element, be it an Item Type, an Item, a Characteristic, or a Stock Item, I just have to add a new record rather than having to add a new field or table. Another table listing additional possible features (rinse cycle, automatic timer, oven capacity) and then a table which links ItemTypes with features (washing machines have rinse cycles but no oven capactiy) another linking individual items with details about their features (the Candy Model 3R Washer has 3 different rinse cycles) Link the Item table to the ItemsInStock table which has contains those characteristics that stock items are most likely to have in common (RRP, Minimum number to keep in stock, Serial Number). A better design would have been to have had one table listing all the ItemTypes (washing machine, dishwasher, microwave), one table linking the itemtypes with Items, eg Washing machine in the ItemType table is linked to Candy Model 3R in the Item Table. He also has to create elaborate union queries whenever he wants to interrelate the items and then, when he needs to add a new characteristic ,(solar-powered) the whole thing needs to be changed again. When he later decides to stock microwaves, all his forms/reports etc will need to be redesigned. A typical example would be someone with a stock-keeping database who, because different items have different characteristics, has a one table for washing machines, one for cookers and one for dishwashers. If this isn't the case, then please ignore me and just tut impatiently. ![]() I say this because your form shows a series of tables which seem to have a lot of characteristics in common. It looks, from the picture of your form, (though looks can be deceptive) as if you may be putting yourself in the position where you need to add a new table when you want to add a new element to your database. I guess not, so I'll go ahead and mark it as solved. If your database is well structured, you will be able to use the same query for all your Subsubforms: if each subform is based on a different table (naughty you!) then you'll need a seperate query for each subform.įarmkid wrote:I was waiting to mark this as solved hoping that evwool would answer the question I asked in the last post about proper database structure. 42&t=24906, in FrmOrders you will see this in practice. If you have a look at my 'Work In Progress' database, here. In Properties, take off the navigation bar. In Form Navigator, drag this Table Control onto your new Subsubform.Ĭlick the Add Fields button and drag the Sum field into your subsub form. Next to Link Master and Link Slave choose the M圜lass field.Ĭlick the More Controls button and drag a Table control onto your form. Give the new form a name you'll recognise.īase it on your query ie right click on it and go to PropertiesĬhoose Query next to Content Type. In Form Design, click the Form Navigator button, right click on your subform there and choose New Form. You should now have only one row in your query and 2 cells. Under the Number field in the Functions row, choose Sum. ![]() Which had a 1 in it, then put the M圜lass field and the Number field in your query. So to expand on his answer, since you say you are new to this (I don't know how your database is structured so my instructions will need to be adapted to suit):Ĭreate a query which contains the 2nd column and a field which links it to the subformĮg if all the fields in subformA referred to M圜lass1 and there was a field called M圜lass in the table You don't get Calculated fields like you do in Access, but Base is much nicer about subformsīased on other subforms, so what you'd actually do is a one row/one visible column Subsubform linked to each subform, as eremmel suggested. I know you asked for a Running Sum, Farmkid, (which is what you get in a bank statement, where each total refers to the line the above), but this sounds as if all you want is a single total of the second column of each subform. What I want is a box at the bottom of or below each subform with the total of the numbers in the second column of that subform.
0 Comments
Leave a Reply. |