Search results

I think that's probably true. Does the IN operator make use of the indexes? It can be expanded algebraically to a set of OR operations, in which I'd guess case it would. I always found the InParam function efficient enough in my line of work as an Environmental Planning Officer, but we were...

If code similar to Tom's in post #3 is used to build a delimited value list, by assigning the value list to a text box control in a form, the control can then be referenced as a parameter in a query by calling the InParam function from the following module published by Microsoft many years ago...

That's not a matter of normalization. SUPERVISORCODE_I is a candidate key, and it's perfectly legitimate to make it the primary key of the referenced table, and the foreign key of the referencing table. In some contexts there are advantages in using a 'natural' key rather than a 'surrogate'...

Line numbers can also be returned in the subform's RecordSource query. The most efficient method is by means of a join of two instances of the table. The following is an example which numbers rows sequentially within each subset of rows per customer ordered by date: SELECT COUNT(*) AS...

This should be done before inserting a row into the referencing table. If a new order is being inserted in the parent form, referential integrity would otherwise be violated if the row has not yet been saved.

Agreed!

Here's a little procedure I added to a contacts form to add a new employer in a subform: Private Sub cmdAddEmployer_Click() With Me.sfcEmployers.Form.Recordset .AddNew .Fields("ContactID") = Me.ContactID .Fields("EmployerID") = 1 .Update End With...

That returns a reference to the subform, it doesn't move focus to it. In fact the subform is not really relevant to the problem, which is that focus is not returned to the text box in the parent form. The Me keyword will continue to return a reference to the class in which the code is...

That would be my natural conclusion too, but they say 'The people scan the barcode on the parent form on the control called txtProductCode that works fine no issues data is transfered to the subform', which suggests that the AfterUpdate event procedure's code is being executed. What surprises...

It seems to me that the OP already has the table he needs for recording the membership of the prayer groups in a manner satisfactory to his preferences. It would benefit from some minor improvements such as the splitting of the member's name into FirstName and LastName columns, and the creation...

A History table is unnecessary, it's inherent in the model: SELECT M1.FirstName & " " & M1.LastName AS Husband, M2.FirstName & " " & M2.LastName AS Wife, WeddingDate FROM Members AS M2 INNER JOIN ( Members AS M1 INNER JOIN Marriages ON M1.MemberID =...

What you describe is an example of such a basic one to many relationship type, that I find it hard to believe it will help in understanding a more complex model. In my experience simplification of an issue, though well intentioned, tends to result in obfuscation rather than throwing light on an...

Your model is missing the second instance of the Member table.

See the OP: "Am facing a problem with my Access database of members which I build for a Christian Prayer Fellowship."

As this guarantees that an empty column position can only be Null, you can also set the column's DefaultValue property to N/A. Then execute the following UPDATE query to fill all Null column positions with N/A: UPDATE [TableNameGoesHere] SET [ColumnNameGoesHere] = "N/A" WHERE...

In relational database terms marriage is a relationship between two entities of, in your case, type Member. To accommodate attributes such as WeddingDate the relationship can be modelled by a second table along the following lines: Marriages ....HusbandID (FK) ....WifeID (FK) ....WeddingDate...

Does the relationship between warehouses and products have any non-key attributes, e.g. QuantityOnHand? If so you will need to include a column for this in the 'junction' table which models the relationship. The column should be included in the parenthesized column list in the INSERT INTO...

The following module was published by Microsoft many years to simulate the use of the IN operator with a value list as a parameter: Option Compare Database Option Explicit Function GetToken(stLn, stDelim) Dim iDelim As Integer, stToken As String iDelim = InStr(1, stLn...

If you are simply registering each product to each warehouse, rather then modelling inventory, i.e. there are no non-key attributes of the relationship type, then you do not need to model the relationship type by a 'junction' table. You can simply return the Cartesian product of the two tables...

Why are you storing the staff count per supervisor, rather than computing it with a simple query like this: SELECT tblPeopleStaffSupervisors.SuperVisorCode_I, tblPeopleStaffSupervisors.FirstName, tblPeopleStaffSupervisors.LastName, COUNT(*) AS StaffCount FROM...