Search results

Avoiding optional foreign keys is not a good reason to work with junction tables. A junction table establishes a many to many (N:N) relationship, and as such should only be used if that is the case. If there is a zero to 1 (0:1) relationship, then an optional foreign key is actually best suited...

Hi, thanks for your answer First of all i must admit that i made a mistake in my analysis: the BeforeUpdate event is also NOT triggered in Ms Access 2002 when the field keeps its default value. It is triggered though when you first enter a value, move to another field, then return to the field...

It's hard to judge for me because i have MS Access 2002, but what caught my attention is the following expression: lstSuppliers.ItemData(lstSuppliers.ListIndex) This is unnecessarily complex. The ItemData property returns the data in the bound column for the specified row. So this expression...

Suppose a form (frm_colr) is based on a table COLR (Colors) with the field colr_id as primary key. To open this form on a specific record, pass the primary key of that record to the OpenForm command in the OpenArgs argument: DoCmd.OpenForm "frm_colr", , , , , , 3 The Onload event of frm_colr...

I have developed an application in MS Access 2002 that administers members and donations for a charity organisation. In one of the forms the so called gift aid declarations are maintained. A gift aid declaration consists basically of a from date and an until date. The from date is mandatory, the...

You are prompted for a parameter value, because you explicitly create one, namely [Old Tel No#] in this example. Obviously, that is not what you want. Furthermore, in this way you cannot get your filter to work, because it will just compare two textstrings, evaluating to either True or False...

track each copy and normalize everything From my 20 year experience i have learned to always fully normalize the datamodel. It will result in a more solid system, that is easily expandable. You are dealing with the following entities: Music Title Composer Season Copy Person My first advice...

breadth first or depth first? Doing it your way, level by level, means traversing the tree breadth first. That should be done only if it is really necessary, because it involves more coding. Doing it my way means traversing the tree depth first, and can be established with very little coding...

you should use recursion A tree is a typical recursive structure, so to generate it, you should use recursion. Suppose your table structure is as follows: JOBS jobs_id (long) = primary key jobs_code (text) PART part_id (long) = primary key part_code (text) part_idP (long) = optional foreign...

work with one table! You should really set it up as simple as possible. Why taking the trouble of working with 2 tables, comparing duplicates, and deleting them? If i understand your functional description well, this is not necessary. Just try out the following. Create one table for all your...

intersection table From what i understand there are 3 main entities involved: Project, Employee and Review From your description i conclude that there is a many to many (N:N) relationship between Projects and Employees: So the advice you got in the previous post is not good: That would...

Why don't you just use one table with an email address and an email_sent field (either a date or a boolean flag)? Instead of maintaining two tables, you could distinguish between the two by having the email_sent field empty or not. I think that would save you a lot of unnecessary work.

relations between tables From what i understand of your functional description there are 5 concepts: Project, Container, Roof, Electrics, Floor The question is whether Roof, Electrics and Floor are really separate entities. They all refer to spare parts and if the structure of their...

DeleteQueryDef does not exist ... this is the solution DeleteQueryDef does not exist ... this is the solution: db.QueryDefs.Delete (<query name>)

extract the month from a date I did not understand your whole story but the DatePart function might help you out: DatePart(<interval>, <date>) Interval can be any of: yyyy (Year) q (Quarter) m (Month) y (Day of year) d (Day) w (Weekday) ww (Week) h (Hour) n (Minute) s (Second)

test query expression ... add a space after c.EmpleadoID, since now it will be concatenated directly to WHERE

first look at your datamodel A succesful database application is founded on a sound Entity Relationship Model (ERM). When i took a look at your database, it occurred to me that you have 2 tables for one entity: emails. They are distinguished by a property, namely if they are sent or not...

make a dynamic query with CreateQueryDef Hello, You cannot parameterize field names in a static query. You could, however, make a dynamic query, either temporary or permanent. First of all you will need a reference to Microsoft DAO 3.6 Object Library (in VB menu Tools: References). Make sure...

delete query solution Hi, Suppose you have 3 tables, of which 1 is an intersection (or junction) table: OBJE (Objects): obje_id (long) = primary key obje_name (text) COLR (Colors): colr_id (long) = primary key colr_name (text) OBCO (Object-Colors): obje_id (long) =...

SQL is not correct: GROUP BY clause is missing This should of course be: Select A.CDName, Sum(B.TrackTime) From tblCD As A Inner Join tblTracks As B On A.CDID = B.CDID GROUP BY A.CDName Having Sum(B.TrackTime) < 900