Search results

It's important to remember that, in a relational database there are two types of tables, base tables and the result tables of queries. It's also important to understand that a fundamental principle of the database relational model is the Information Principle (Codd's Rule #1). This requires...

There is no Assemblies table, only the Parts and PartStructure tables. The former contains all 'parts' i.e the complete assembly, all sub-assemblies and all atomic base parts. PartStructure models a binary many to many relationship type between two instances of the parts table: The...

You can easily restrict the rows inserted into the local table by restricting the INSERT INTO statement by means of the NOT EXISTS predicate, so there would be no need to specify dates. This is more reliable that relying on the dates as it would also insert any rows with dates prior to the...

Which is exactly what my BoM file does. I first built it around 25 years ago using one of the standard algorithms published by Chris Date in the Introduction to Database Systems, but having generated the BoM I wanted to aggregate the weights of the atomic base parts which made up the...

If we start with a table Parts with columns for it's attributes, each of which must be functionally determined solely by the key: Parts ....PartID (PK) ....PartName ....Tag Parts are combined to make assemblies, which is another entity type modelled by a table: Assemblies ....AssemblyID...

This can also be done without the need for a CurrentEntry query to return the latest row. The requirement is analogous to the very common task of reading from instrumentation, where the current and previous readings are returned in order to compute the consumption between readings. The...

That filters the form to the one record. Rather than doing that, open the form and then navigate to the record: Const MESSAGE_TEXT = "No matching record found." Dim frm As Form Dim intJobNumber As Integer intJobNumber = Me.txtJobID DoCmd.OpenForm "frmTodayNew", acNormal...

The fault is probably mine.

Take a look at the option to use a simulated split form in the attached little demo file. If you use this as a model for what you are doing you should have no problems.

I think that's true. As far as I can see the entry_date column includes the time of day, so the first thing to do is ensure that, in table design view the column is indexed uniquely (no duplicates). You then don't need a date_time_stamp column. If you've set its DefaultValue property to...

You'll need to put code in the subform's AfterInsert event procedure to requery the parent form and move to the newly inserted record. The following is code from one of my demo files which does this: Private Sub Form_AfterInsert() ' requery parent form to include new record ' and...

I would not rely on an autonumber column to indicate the sequence of insertion of rows into the table. As you've pointed out this would fall down if the rows are not inserted in entry_date order. The value cannot be manually corrected as autonumbers cannot be edited. Instead I'd suggest you...

So how do you determine the distribution of the payments among the six outstanding invoices? If you give priority by invoice date for instance, and the payment covers three with a remainder, then how do you indicate that the fourth invoice is partially paid?

Firstly let me make clear that in the context of a many to many relationship type my view is that a parent form/subform will in most cases be the appropriate interface. This is not a matter of necessity, however. Separate 'linked' forms can equally well be used if preferred. The referencing...

As has been noted in the AccessForums.net forum and here you have an Access table masquerading as a spreadsheet. If that's what you want why not use Excel? In a relational database the correct way to model such data would be to enter each transaction in a separate row in a table. With a...

Is the PIN ID column shown in your screenshot a concatenation of two separate columns, or is it an actual column in the Pins table? What I would expect with a simple one to many relationship like this would be the following tables: Connectors ....ConnectorID (PK) ....ConnectorName ....etc...

It's much the same as when you run the query in the user interface, and it warns you that a certain number of rows won't be inserted for a stated reason, in this case a key violation. In the example I posted for the OP I tried to keep things simple, but I have to confess that it's not great...

Tom, I deliberately omitted the dbFailOnError constant in my earlier reply to the OP. This allows additional trainees to be added to a training session by executing the procedure again for that session. The errors which are raised on those rows which attempt to insert a trainee who has already...

Did you try the amendments posted by MajP? I think you just need to change the assignment of the concatenated string expression to the strSQL variable in Post #13 as follows: strSQL = "INSERT INTO TrClassesT(JR_ID,TrnDate,TrnType) " & _ "SELECT AFD_ID, #" &...

The second # sign delimiter around the date is missing. It should be: JR_ID, #" & format(me.TrnDate,"mm/dd/yyyy") & "#,'" & me.TrnType & "'" PS: For consistency with the rest of the string expression you might want to use a pair of contiguous double quote characters, "", to represent the...