Search results

I've attached a little demo file for reserving hotel rooms. In this think of occupants as analogous to visitors in your case, rooms as analogous to type of booking. For each booking the total cost is returned with the following function: Public Function TotalCost(intRoomNumber As Integer...

Provided the query is updatable. A simple query based on a single table, where no values are aggregated in the query, will be updatable. If the query aggregates values e.g. returns the sum of the costs of all items ordered, grouped by customer, then that query won't be updatable. You'll...

Don't you just need two tables, one to model the assets ledger, the other to model the liabilities ledger? When a row is inserted into one code to insert a corresponding row into the other would be executed. I've attached a little demo to illustrate this. Data entry is via subforms in an...

One point of clarification perhaps worth making is that atomic base parts need not, and in most contexts will not, necessarily be physically atomic. In the manufacturing company in which I worked early in my career a distinction was made between production inventory and general inventory. The...

The simplicity of the adjacency list model on which my demo is based should make it relatively easy to populate the PartStructure table, which is the heart of the database. Having created the Parts table by inserting a row for every product assembly, sub-assembly, and atomic base part...

If you are storing the company, branch and employee in the form's table then the table is not normalized to Third Normal Form (3NF) Employee functionally determines branch, and branch functionally determines company. 3NF requires that all non-key columns are determined solely by the whole of...

One way to return the last two events per school in a single row would be by means of a crosstab query. This would have the advantage of identifying the events as column headings: TRANSFORM SUM(TotalSales) AS MinOfTotalSales SELECT SchoolID FROM ( SELECT...

Probably the most efficient solution would be a JOIN of two instances of your query: SELECT Q1.SchoolID, Q1.EventID, Q1.TotalSales FROM YourQuery AS Q1 INNER JOIN YourQuery AS Q2 ON Q2.EventID <= Q1.EventID AND Q2.SchoolID = Q1.SchoolID GROUP BY Q1.SchoolID...

I find the theoretical stuff fascinating. I greatly regret that I never learnt mathematics to a more advanced level, to be able to understand the basis of theory better. I have an inkling of what the relational algebra is all about, but I'm lost when it comes to the relational calculus.

You seem to want to do two things, (a) save a report's output as a PDF file and (b) email a report as a PDF attachment. For the first you need to build the full path to the folder where the file will be saved, and then concatenate the current FSEJobNo value and the .pdf extension to it as the...

Date has some interesting things to say on this:

On the contrary. In my BoM.accdb file you simply select the product in the list box on the left side of the main form, and it's components are then shown in the subform on the right. In the simpler PartsTree.accdb file the quantities are shown rather differently. Whereas in BoM.accdb the...

That is correct, but in most contexts you do not need to, nor should you, insert the FirstName and LastName values into the referencing table on which the form is based. That would in most contexts introduce redundancy and the consequent risk of update anomalies. Take a look at the image of a...

You can model products, assemblies (which might be sub-assemblies), and atomic base parts very simply with two tables. In one table each product, assembly and base part is a value in the same column in the table. The other table simply references two instances of the first table by referencing...

This sounds like a candidate for a hybrid control. Such controls are often used for correlated combo boxes in continuous forms view, where a text box is superimposed on a combo box, but can equally be used where one text box is superimposed on another. I've attached a little file as an...

I cannot think of an occasion where I have allowed a column of Currency data type to be Null. Invariably I'd set the Required property of such a column to True (Yes), and its DefaultValue property to 0. Null is semantically ambiguous. It is not a value, but the absence of a value, so has no...

The following code is from one of my demo files and outputs the report as a PDF file with a path such as: C:\Users\kenws\Documents\Databases\InvoicePDF\ACME Flanges\ACME Flanges 20150424134202.pdf The file name is obtained from the second column of a bound Customer combo box in the current...

If data is being encoded in table names you should certainly do that. A fundamental principle of the database relational model is the Information Principle (Codd's Rule #1). This requires that all data be stored as single values at column positions in rows in tables, and in no other way. This...

The attached file illustrates how non-normalized data from Excel (or any similarly structured source such as a delimited text file) can be decomposed into a pre-defined set of normalized tables in Access. When inserting the appliance data into a table, your current string expressions can be...

Be sure to have a referenced Lanes table, with a single column Lane as its primary key. Then create a relationship between this and the trees table and enforce referential integrity. This will protect the integrity of the database by ensuring that only valid Lane values can be entered into the...