UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V  1 2 >  (Go to first unread post)
   Reply to this topicStart new topic
> Looks Normalized But Doesn't Work    
 
   
bburn
post Sep 16 2011, 05:40 PM
Post#1



Posts: 197
Joined: 2-July 09



I make historic windows. I need to generate a cut list for all the types of windows I make. I want a table in which to record my contracts. Any given contract may included Casement windows, Double-Hung windows, and Doors. There are many specificaitons unique to each of those groups of units. However regardless of the type of unit, there are certain specifications that are common, such as height and width.
If I create a single table with all the fields possible for every unit there will be a great number of blank fields. For example, every field "hinge location", which is necessary for Casement windows and doors, will be empty in all the records for Double-Hung windows. Conversely, every field "Meeting Rail Thickness" which is necessary for Double-Hung windows will be empty for all Doors and Casements.
Therefore, I created three tables to contain the General Specifications for each type of unit; tblGenSpecHung, tblGenSpecCase and tblGenSpecDoor. Since all types of units will have the common fields Height and Width I created tblUnit which contains those fileds as well as several other fields common to any unit I make regardless of the type. To complicate matters further, one unit may have grids (muntins) and another of the same type may not. There are three or four other details that may differ between units of the same type and these details have unique characteristics of their own. Those are represented by tblMuntin and tblLug.
The contract on which I'm now testing is a perfect example of my needs. I must make 14 double-hung windows, 10 casement windows and two doors for the same customer on the same contract. Some of the windows have grids and some do not.
I have tblContract
ContractID PK
ContractDesc
etc.
tblGenSpecHung
GenSpecHungID PK
ContractID FK
spec1
etc.
tblGendSpecCase
GenSpecCaseID PK
Contract FK
spec1
etc.
tblGendSpecDoor
GenSpecDoorID PK
Contract FK
spec1
etc.
tblUnit
UnitID PK
GenSpecHungID FK
GenSpecHungID FK
GenSpecDoorID FK
UnitSpec1
etc.
tblMuntin
MuntinID PK
UnitID FK
Details...
tblLug
LugID PK
UnitID FK
details...
My problem is that when I create the form/subs to do this, Access refuses to allow me to enter, for example, a new double-hung record in tblUnit because there is not a related record in tblGenSpecCase or tblGenSpecDoor. How can I arrange things to allow this? I've heard of link tables but don't know how they work. Is there a work around or do I have things designed improperly?
(Thanks for all your help Mike, I never did get an answer to this question though)
I've attached a sample db I've been testing on.
Bill
Attached File(s)
Attached File  CutListV1.zip ( 31.04K )Number of downloads: 7
 
Go to the top of the page
 
tina t
post Sep 16 2011, 06:52 PM
Post#2



Posts: 5,919
Joined: 11-November 10
From: SoCal, USA


rather than try to build tables for each variation, i'd suggest a more flexible approach: tblContracts, for the data the describes each specific contract - you already have this, looks like. tblTypes, as a simple list of all the window types you make, as
blTypes
typeID (primary key)
typeName <a text field, to store the name of a window type>
tblTypes
typeID typeName
1 Casement
2 Double-Hung
3 Doors
...
...
...
this is an almost static table; once you populate it, you'll only add a record if you start making another window type.
tblCharacteristics, as a simple list of all the available characteristics, whether they may apply to one type of window, or many, as
tblCharacteristics
charID (pk)
charName <a text field, to store the name of a characteristic>
tblCharacteristics
charID charName
1 Height
2 Width
3 Hinge location
4 Meeting rail thickness
...
...
...
this, too, almost static. after initially populating the table, you'll add new characteristics only when there is a characteristic, for any window type, that is not already listed.
next, you'll need a table to list all the characteristics of each window type. tblTypeChars, with one record for each combination of a type and a related characteristic, as
tblTypeChars
typecharID (pk)
typeID (foreign key from tblTypes)
charID (foreign key from tblChars)
tblTypeChars
typecharID typeID charID
1 1 1
2 1 2
3 1 3
4 2 1
5 2 2
6 2 4
another nearly static table; it will be used only to "pre-populate" tblContractTypeDetails (listed further below) to make data entry easier in your forms.
now that you have defined all window types, all characteristics, and all the characteristics that apply to each window type, you have the data you need to enter the window type(s) ordered on specific contracts. you'll need a tblContractDetails, one record for each window type ordered under each contract, as
tblContractTypes
contypeID (pk)
ContractID (foreign key from tblContracts)
typeID (foreign key from tblTypes)
Quantity (the number of windows of this specific type, ordered under this specific contract)
<any other fields that describe this specific window type for this specific contract - maybe price? or date to deliver? or nothing, if you don't need to track any other data>
tblContractTypes
contypeID ContractID typeID Quantity
1 1 2 2
2 1 3 1
3 2 2 1
so, in the example above, contract one orders two double-hung windows, and one door; contract 2 orders one double-hung window.
now you need a table to enter the specific values for each characteristic of each type ordered on a specific contract. if a contract orders more than one item of a single type, but with different characteristic values, then you'll need a separate record for each - for instance, a contract orders three casement window, two identical, and a third of the same height but a different width. you'll need one record for the two identical, and another record for the different one.
tblContractTypeDetails
detailID (pk)
contypeID (foreign key from tblContractTypes)
charID (foreign key from tblCharacteristics)
detailValue
tblContractTypeDetails
detailID contypeID charID detailValue
1 1 1 36"
2 1 2 48"
3 1 4 .5"
relationships are
tblTypes.typeID 1:n tblTypeChars.typeID
tblCharacteristics.charID 1:n tblTypeChars.charID
tblContracts.ContractID 1:n tblContractTypes.ContractID
tblTypes.typeID 1:n tblContractTypes.typeID
tblContractTypes.contypeID 1:n tblContractTypeDetails.contypeID
tblCharacteristics.charID 1:n tblContractTypeDetails.charID
this design is completely expandable, allowing for any number and combination of contracts, window types, and characteristics, and allowing you to add any of those without having to build new tables, queries, forms or reports. suggest you read up/more on relational design principles, hon, so you'll understand the setup better. once you have the tables set up, you can build forms, using subforms and combobox controls, to pull it all together and make for easy data entry. i can show you a "trick" to utilize tblTypeChars to make entering data about specific window types for specific contracts faster and help you avoid forgetting a specific characteristic that you need to track - while allowing you to enter additional characteristics that are not part of the "default" list, or conversely automatically deleting specific characteristics that don't happen to apply to a specific type for a specific order.
luckily for me, your db is in A2000 format, which i can open. so if you're having trouble following the above setup, you can post back and i'll throw together a quick demo for you and post it.
hth
tina
Go to the top of the page
 
Jeff B.
post Sep 16 2011, 06:54 PM
Post#3


UtterAccess VIP
Posts: 10,242
Joined: 30-April 10
From: Pacific NorthWet


One of the (many) advantages from using a structure/design like what Tina offers is that Access is optimized to work with data organized that way. As you've already found, organizing your data the way you did makes both you and Access work much harder...
Go to the top of the page
 
bburn
post Sep 16 2011, 10:23 PM
Post#4



Posts: 197
Joined: 2-July 09



Thanks Tina,
That's a paradigm shift for me. I'll study your scheme and see if I can wrap my head around it. But, as I do, let me make one significant point...95% of the double-hungs have the same general specifications. The same with casements with a different set of genspecs. That's why I approached it the way I did. So I'd have a table of genspecs (with the defaults set) that would cover the bulk of all the units of the type I'd make, with only occasional execptions. I haven't looked at what you're recommending long and hard enough to see it would do the same thing but I hope it will.
Olook forward to seeing your "trick"
Bill
Go to the top of the page
 
tina t
post Sep 16 2011, 10:55 PM
Post#5



Posts: 5,919
Joined: 11-November 10
From: SoCal, USA


well, that's easy enough to support, and still fits right in with the "trick" i mentioned. just add one additional field to tblTypeChars, as
blTypeChars
typecharID (pk)
typeID (foreign key from tblTypes)
charID (foreign key from tblChars)
typecharDefault
you can set the initial value of each characteristic assigned to each type, and include that value in the "trick" of prepopulating data entry records in a form, for each specific type ordered on a contract.
yes, and i understand, believe me. i've been writing basic-to-journeyman-level Access databases for over a dozen years, and i still must say that the principles of relational design were the hardest concept for me to grasp, out of everything i've learned to this date. some people catch onto it easily, some of us don't. (IMO, the more someone has worked with Excel spreadsheets, often the harder it is to shift to a relational data way of thinking.) but no worries, once you've trained yourself to think that way, it becomes second nature.
hth
tina
Go to the top of the page
 
bburn
post Sep 16 2011, 11:48 PM
Post#6



Posts: 197
Joined: 2-July 09



OK Tina,
see what you're trying to do. IMHO I think it should work for me. I've got the tables set up and was trying to populate some with accurate data but got some messages about related records. I'm guessing I need to setup some forms through which I can input the data you term "practically static". Please check my relationships and make some recommendations concerning to to set up forms to input this static data.
I do hope this works because I also make a variety of storm windows with much the same variations as I described concerning the prime windows. For this I have another db that didn't give me the problems this one did. I'm guessing with a little work I can have just this one for all my various prodcuts. I'm looking forward to enjoying the "flexibility" you say I'll have.
Attached is the zipped db "work in progress" that I just created.
B
Attached File(s)
Attached File  CutListUA.zip ( 14.63K )Number of downloads: 6
 
Go to the top of the page
 
bburn
post Sep 17 2011, 08:52 AM
Post#7



Posts: 197
Joined: 2-July 09



Tina,
think our last posts crossed in the air. It appears Mike Smart is helping too. He responded to an earlier post and we've been communicating via email since most of our discussions were details in which no one but me would be interested. He took the db I posted last to this thread and created some forms that look really nice.
After cogitating all night about the direction we're now taking, I opened the db again and tried to reacquaint myself with your take on the project. I do get the theory. One of my challenges is component naming. The names you assigned to the tables and fields make sense but don't immediately register with me. It's like beginning a foreign language – I know the word but must translate it in my head before I see the image. The words I chose make more sense to me because I’ve been using them and they are somewhat common in this industry. Correct me if I’m wrong in my translations.
Contract – self explanatory - Contract
Type – The type of unit or style – Style
Characteristic – the name of the particular specification that will be assigned a value in another table – Spec
TypeChar – the table that stores the list of specs for a particular style of window (Unit) – StyleSpec
ContractTypeDetail – the table that stores the actual value of a given spec for a given style of window (Unit) in that particular Contract –UnitDetail
To be sure we’re on the same page let me describe what you say as I interpret it, keeping in mind that the whole reason for this DB is to generate a series of reports (cut lists) with which “I can make sawdust” when I take them into the shop. I also need to generate reports that I can use to order glass and wood.
The names of all the potential Types (Styles) of units I could possibly make are stored in the table tblType (tblStyle).
The names of each and ALL of the several dozen Characteristics (Specs) that describe ALL Units are kept in the table tblCharacteristic (tblSpec).
The table tblTypeChar (tblStyleSpec) ties the type (Style) of Unit to those characteristics (Spec) required to completely describe that Type (Style) of Unit.
tblContractType (tblContStyle) contains the specific types (styles) of units that are included in any particular Contract.
tblContractTypeDetails (tblUnitDetail) contains the actual values of each of the elemental specifications for a individual units.
After inputting the default values for all the characteristics (Specs) one time, I’ll be able to either accept the default values or edit them. As I write this I can envision a twist that needs to be accounted for. That is for example…A particular contract calls for twenty double-hung windows. They are have all the default characteristics as my standard DH except the bottom rail is wider than the default value. It would appear from what I understand of this setup that I’d need to remember to change the value of the BRWide field for every one of the twenty units. How do we handle that? If I had to make an expecption for twenty units I'm sure to make a mistake on at least one of them.
Tina, I have a lot more questions and not a lot of time. I have four orders (80 windows) for which to order materials, like yesterday. Should we continue to do the forum post thing or are our exchanges now too detailed for the common good. Maybe email? Either way is fine with me except email is faster and we should include Mike.
Thanks SO much. Oh and BTW my cheesy web site is www.heirloomwindows.com in case you want to see what you're helping with.
B
Go to the top of the page
 
mike60smart
post Sep 17 2011, 09:43 AM
Post#8


UtterAccess VIP
Posts: 13,129
Joined: 6-June 05
From: Dunbar,Scotland


Hi Cynthia
created some Forms based on your structure
Attached File  CutListUA.zip ( 62.71K )Number of downloads: 4
Go to the top of the page
 
tina t
post Sep 17 2011, 10:59 AM
Post#9



Posts: 5,919
Joined: 11-November 10
From: SoCal, USA


remember that all detail records apply to a specific contract unit record. so if twenty units have identical specs - doesn't matter whether they're the default or not - then you'll have one contract unit record with quantity = 20, and a single "set" of detail records with the spec values for that contract unit. if you had 17 double-hung windows with identical specs, and 3 double-hung windows with identical specs had at least one spec different from the other 17, then you would have two contract unit records, one with quantity = 17 and the other with quantity = 3, and a "set" of detail records for each unit record.
xcellent, got it in one!
i'm going to throw together a demo and post it for you to review - that should be quicker than a lot of back and for posts (or emails). i'll use the design i posted, but the with the names that you posted because, as you say, those are the accurate terms to describe your business. it'll take me a couple hours.
hth
tina
Go to the top of the page
 
bburn
post Sep 17 2011, 11:41 AM
Post#10



Posts: 197
Joined: 2-July 09



That's perfect. A local Theater group I'm in is doing "Our Town" and we have a matinee this afternoon. I have a small part. I'll be back around 5:00 EST to have at it again. I assume you're PST?
couple of questions on the side...
a) Where do you find the time to work several hours on a db project for a total stranger?
b) What motivates you to do this?
c) Even my teenagers don't know what your salutation hth means. Clue us in?
d) How about completing your profile? I'm a people person and love to get to know folks. As a matter of fact there's a line in the play that I'll hear again today..."You know our town, we like to know the facts about everybody"
And one other thing concerning my app....
You get what I'm doing for the most part. What I'm not sure you understand (or maybe in your recommendation it doesn't matter) is that there usually will be three or four sets, sometimes many more, of units that are identical except for their overall height and width. In my previous db (the original I sent you), they would all belong to the set Double-hung and there would be a record in that table with all the GenSpecs. Then each unit would have it's height and width as a record in UnitSpec (along with unitLocation). So, if I had 20 DHs in a contract that all have the same GenSpec (as is most frequently the case), I'd have only one GenSpec record and twenty UnitSpec records. If the situation was like you described in your most recent example, I'd have two GenSpec records and 17 tied to one and 3 tied to the other. I still don't grasp your method firmly enough to see if that makes any difference. And one other thing...
Because I install my own products, I like to record the unit location, usually specified by a description of the room the unit is in (Bedroom1, Bedroom2, Diningroom1, etc.) along with a number for that unit within the room (Bedroom22 for the second unit in the second bedroom). If we included that data in the details table, wouldn't we then have as many ContractUnit records as detailrecords or am I not thinking correctly? Either way where would we put the UnitLocation field?
Go to the top of the page
 
tina t
post Sep 17 2011, 01:38 PM
Post#11



Posts: 5,919
Joined: 11-November 10
From: SoCal, USA


a) oh, i should be doing something else. but sometimes a situation captures my imagination - i can see how it comes together...and you've picked up so fast on the relationships that i outlined, i think it'll be time well-spent to make a demo for you. i think if you choose to study it that you'll learn a lot about basic design, of tables and forms; tearing apart somebody else's db is a great way to learn and get ideas.
) i love building dbs in Access, and i learn something almost every time i build one, even simpler ones.
c) hth = hope this helps
yes, i did understand that. it seems like we're duplicating data, i know, in having a complete spec list for each "set" of identical units, even when two different unit records may only have only a different value in one or two specs. but that's what allows for a completely flexible setup, because you're always going to have that exception, sooner or later, where something that you consider a "general spec" has a different value for this one particular contract.
part of relational design is building to support future changes (within reasonable bounds) without having to change structure, interface, or code - only data changes are required. if i had a dollar for every time a somebody told me "It is always this way", or "that will never happen" and then came back a few weeks or months later and said "well, this changed, or that happened - and how do i put it in the database?" - well, i could retire right now and live happily ever after!
hth
tina
Go to the top of the page
 
tina t
post Sep 17 2011, 01:52 PM
Post#12



Posts: 5,919
Joined: 11-November 10
From: SoCal, USA


yes, that does make a difference. i'll give some thought on how best to incorporate it into the design. as always, the key will be to define the additional data relationally - what subject/entity does a particular characteristic describe? what other data does it depend on, and what other data depends on it?
Is always with a relational database, the devil is in the details. and this situation just highlights the pitfalls of designing a database without thoroughly analyzing the process that the database will support. of course we didn't do that here, so don't be surprised if the demo doesn't support other elements that have not come to light in this particular forum topic. for one thing, any mention at all of clients is conspicuously absent.
hth
tina
Go to the top of the page
 
bburn
post Sep 17 2011, 05:45 PM
Post#13



Posts: 197
Joined: 2-July 09



Tina,
appreciate the interest you've shown in my challenges. I really think yours is the direction to go, we just need to work out some of the details. Furthermore, the paridigm has shifted and I understand why we're doing what we're doing. It will be helpful in my future db work. As a matter of fact in my spare time I'm working on a very comprehensive contact list for all the various people and their involvement with our local community theater. The DB design is the way to go there too.
Just so you know for sure what I'm seeking in terms of output, I've attached a cut list for storm window projects. I was looking for my original db for prime windows (this application but I must have modified it because I couldn't find it). The storm db is very similar in input, output and design to the prime window db. They both have worked for me in the past.
Please take a quick look at a couple of the reports rptRails, rptStyles, rptAssembly and the relationships.
Ogenerally take 12' random width boards (between 6" and 12" wide) and cross cut them a little longer than necessary for the lengths of my styles (vertical frame members) and rails (horizontal frame members). I then refer to the appropriate cut list to cut them to the exact length. I mortise the Styles so I need to know how far apart each mortise cut will be and I tenon the rails so I need to know how far apart the tenons are. Of course I refer to the cut lists for this info.
Got to go now. Another performance of "Our Town" tonight.
TTFN
B
Attached File(s)
Attached File  CutListStorms.zip ( 201.68K )Number of downloads: 2
 
Go to the top of the page
 
tina t
post Sep 17 2011, 07:04 PM
Post#14



Posts: 5,919
Joined: 11-November 10
From: SoCal, USA


okay, here's a demo db for you to review, tables/relationships/forms. it's not polished, but that's what happens when you throw something together in a hurry. ;) i wanted you to see the tables and relationships, AND see how a user interface could pull it all together from a "human" standpoint, and make the data easy to enter, review, etc. a couple things to keep in mind:
) i changed a one or two table names to use the terminology you used in your post - namely "units". made more sense to me, so see what you think.
2) if you test-use the interface, you'll probably find that it's not the best or most intuitive setup for you. and that's no surprise, because a process analysis includes a complete review of the user's work flow, so that the user interface can support it and even make it easier and more efficient. and we didn't do a process analysis...
3) i tried to keep the code to a minimum, and keep the setup as simple as possible, so that you can learn from it without being overwhelmed, and so it's easier for you to tinker with. the subforms setup in frmContracts is a bet more advanced, but you have "ordinary" mainform/subform setups to compare it to, so i think it will teach you something new with out being to confusing. one suggestion: keep an unchanged backup file, so you can do whatever you want, to anything in the db, and still have a working backup to copy from and compare to.
take a look at ztblDeveloperNotes, which has just a few notes that you might find informative.
Oentered one dummy property, with two dummy contracts. take a look at the older contract record to see details.
yes, that does make a difference. i'll give some thought on how best to incorporate it into the design. as always, the key will be to define the additional data relationally - what subject/entity does a particular characteristic describe? what other data does it depend on, and what other data depends on it?

okay, locations are related to units. because one unit record may be referring to multiple units (quantity field), locations are on the -many side of a one to many relationship with contract units. so i created tblUnitLocations to store the specific locations of the "pieces" referred to by one unit record. so one contract unit record with a quantity of 3, should have three related unit location records, one location for each of the three pieces. since a unit record describes a set of one or more identical units (every spec the same), the location(s) just list where the individuals pieces of that unit set will be installed.
when multiple units for a contract have the same style, but not all have exactly the same spec values, then you'll need to enter more than one contract unit record to cover them. again, look in the older contract record; you'll see two unit records with the same style. move from one unit record to the other - you'll see the location data and specs data change.
post back with questions, of course. when you're familiar with the layout, and the tables/relationships make sense to you, do some work on the reports you want and see how far you can get. then post back with questions.
hth
tina
Attached File(s)
Attached File  db1_tina.zip ( 81.29K )Number of downloads: 10
 
Go to the top of the page
 
bburn
post Sep 17 2011, 09:39 PM
Post#15



Posts: 197
Joined: 2-July 09



Tina,
know how much time your efforts represented. I don't know how to thank you (need any windows?). How bout Colts tickets? :-)
I'm really anxious to work on the db but it's now 10:30. I just finished our last performance of Our Town and have church tomorrow morning then "A Few Good Men" rehearsal tomorrow afternoon. Not only that but I've been ignoring my wife and spending all my spare time with you. She's getting jelous. I better take another quick look tomorrow to let it digest then pick it back up Mon.
Thanks again.
B
Go to the top of the page
 
tina t
post Sep 18 2011, 10:58 AM
Post#16



Posts: 5,919
Joined: 11-November 10
From: SoCal, USA


you're welcome, hon. no rush (i'll be going back to my 10-hour workdays on Tues, myself), and no worries - and don't expect too much out of the db. its' purpose is to show the tables/relationships and illustrate one way a user interface can pull the data together. but not the only way, certainly, and not the best way. with more time, no design limitations, and a thorough process analysis, i would probably build an interface substantially different - and probably at least some differences to tables/relationships - and definitely better. and other developers' designs would be different again. with Access, you'll find that you're limited only by your own available time and willingness to learn, and by your imagination. :) tina
Go to the top of the page
 
bburn
post Sep 18 2011, 07:48 PM
Post#17



Posts: 197
Joined: 2-July 09



Hi Tina,
got around to digging deep into your masterpiece this evening. I've spent about three hours playing around with it and entering all my "Styles", "Specs", then matching them up and adding the default values. I have a few questions (imagine that).
First of all let me tell you how I'll be using this thing...
I'll go to a home and make a sale. I'll go around and measure all the windows, noting the location, unit style, height and width of each jamb and any other peculiarities of the individual units. Once I have all that recorded on paper, I come home and start inputting data.
Property description
Overall contract (job) description
Then those items I mentioned above concerning EACH opening "UnitDims" or "UnitSpecs"
It may be hours, days or weeks before I need my cut lists but at some point I need to run a series of reports as I progress through the job, usually in this order...
Wood Order; Adding all the heights together, multiplying by the widths of the styles then multiplying by the thickness and waste factor to get board feet. Glass Order; For each unit, perform the calcs necessary to determine the glass size then send it to my Insulated Glass mfg.
Cutlist Rails; Calculate the finish lengths of each rail then grouping them together so I can cut all the, for example, 24.625 long rails at the same time. This list is usually grouped by length and sorted descending.
Cutlist Styles: Same thing.
Then many other reports that tell me where to position the mortises etc. all of which need to be for individual units, grouped by dimension.
All of these reports are pretty calculation intensive.
Otried to put together a test query to make sure I understand from whence all my raw data comes but got really confused. Can you give me some ideas on how to assemble the queries which are the most critical part of the db? Also, I really need a form in which I can input the INDIVIDUAL UNIT DATA for OPENING addressed in the contract. Am I missing one that you have included or did we not get around to that?
TX
B
Go to the top of the page
 
tina t
post Sep 18 2011, 09:47 PM
Post#18



Posts: 5,919
Joined: 11-November 10
From: SoCal, USA


hmm, well, the tables design should make that fairly easy, actually. remember that a single contract unit record has a quantity - and every one of the units included in that quantity have exactly the same dimensions. so right away, you can run a query using tblContractUnits and tblUnitSpecs, properly linked in the query of course, to multiple the relevant spec values by the unit record's quantity. suggest you first tailor a query to pull the relevant contract unit records, filtering by style if you need to, and filtering by the specific specs you need for your calcs. once you have all the raw data in a SELECT query (or a Totals query, whatever works best), then you can start putting calculated fields in the query - or in a report, if the calcs won't work at the query level - to give you the numbers you want.
eally, it seems to me that all the queries will be similar, in that you need to start by pulling the raw data you need, and that's going to be "some specs X contract unit quantity" to get the totals of board feet, glass size, etec. so the raw data is in the tables cited above - get the correct records returned by a SELECT query first, then go from there to set up the math. remember that you can use Totals queries to group data at the query level, and use Grouping and Sorting box to set up grouping at the report level. so you have a lot of options of when/where to do math calcs, depending on what works for your needs.
give it a try, and if you're having specific problems querywise, then post back and include the query's SQL statement, what return you're expecting/trying for, and what you're actually getting.
that doesn't sound familiar, hon, but maybe i just don't recognize it. can you describe in some detail what data we're talking about, and how it differs from the data that already has forms provided, pls?
hth
tina
Go to the top of the page
 
bburn
post Sep 18 2011, 10:55 PM
Post#19



Posts: 197
Joined: 2-July 09



Let's start with the forms I need and what I'll put on them. Let's assume I've populated tblStyles, tblSpecs, tblStyleSpecs (because I've got enough data in them to do some testing)
roperty - got it in your add/update properties form "frmProperties"
Contract - the same form includes this info as a sub form of the one above.
Now I need to input the measurements of units that I've just sold...
...each of these records will include the location, jamb height and jamb width of the units, qty and muntin info. Since you want to make me do work that the database could do :-), I guess I'll have to take my little piece of paper on which I've noted all the windows in the house and add up each group that have the same dimensions (before, I just put each individual window in my form and my queries and reports would tell me how many of the same dimension I have).
This is how I get the info...
I'd walk up to a unit and I'd write on a note pad after I measured it, the room, unit number in the room as a description, the style, the height and width and then if the unit has muntins the number of horizontal muntins and the number of vertical muntins in each sash (upper and lower).
Parlor, 1, DH, 32 x 84 1,2 - 1,2
Parlor, 2, DH, 32 x 84 1,2 - 1,2
Parlor, 3, DH, 28 x 84 1,2 - 1,2
Parlor, 4, DH, 28 x 84 1,2 - 1,2
Dining, 1, DH, 26 x 66 1,1 - 1,1
Dinnig, 2, DH, 26 x 66 1,1 - 1,1
And so on...
I would input these in the same manner. Hopefully in a form/sub/sub (Properties/Contracts/Units) with fields in each unit record being Room, Unit Description, Style (radio), Width, Height and muntin information which could be in a seperate sub form that I could pop up with a button if necessary. A room may have two or three different window sizes. But other rooms will likely have the same size windows in them as well. My Victorian home has 32 Double-hung windows, 28 of which are four different sizes with another four odd balls. All of them have what I would call the same GenSpecs, or in other words are identical except for the height and width. And my home is very typical of those of my clientel.
My point is, that unless I'm missing something, there is nowhere to input this MOST VITAL information. This is the key information that all my cut lists are based upon. I'm not opposed to creating the form myself but the design is so radicaly different from what I've been designing for myself that I'm at a loss as to where to begin in creating it. I can't begin to do any testing or creat calc queries until I can input this info and I know it has to be related upwards to the contract and property. BTW contract and property differ because my customers often do the house in stages so...same property multiple contracts.
One other thing. How do you use the quote button on the bottom of each post?
B
Go to the top of the page
 
bburn
post Sep 19 2011, 09:09 AM
Post#20



Posts: 197
Joined: 2-July 09



Good Morning Tina,
I'm really trying to make this work. I've added some tables that I thought we might need. I'd like to hear your thinking on my additions. I've attached your db with my modifications.
I do understand the need for the ALLOWANCE for more than one StyleSpec for each contract. But as you consider this, please keep in mind that IN MOST CASES THERE WILL ONLY BE ONE StyleSpecs record per contract - Most of the time the home will have the same style of windows, they will all be identical except for the Height, Width and muntin configuration. Therefore I've created tables for those Specs that will most likely vary among the same styles of windows in a contract. I also added some fields in tblUnitSpec that most likely will be unique among records.
The fields I've added in tblUnitSpec are; UnitLocationID FK (since it's possible to define all the possible locations in a seperate table), Height, Width, and Notes. I put them in tblUnitSpec because they might apply to only one window in the contract rather than the whole batch of the same StyleSpec.

I added the tables; tblUnitMuntin, tblUnitLug, tblUnitMROffset all with the obvious ID's as their PK and all with a UnitSpecID field as FK. Again, these are all criteria that may vary among tblUnitSpec records but will have the same tblStyleSpec records since everything about the window is the same expect the Height, Width and fields within these tables (mabey unit lug should go into tblSpecs as a field since if one window has lugs the rest will very likely also).
If I'm not mistaken we are still using your overall scheme but adding details, through the use of these other tables, to the tblUnitSpecs records that differentiate them among the tblStyleSpec records but would still be the same tblStyleSpec record.
Am I thinking correctly?
B
Attached File(s)
Attached File  CutListUATinaWorking.zip ( 143.22K )Number of downloads: 2
 
Go to the top of the page
 
2 Pages V  1 2 >


Custom Search


RSSSearch   Top   Lo-Fi    21st May 2019 - 12:10 AM