There are many instances of this with authors' names, e.g. F Scott Fitzgerald, H Rider Haggard (though I did come across one edition of 'She' were the authorship is given as Henry Haggard). In bibliographical contexts I generally use a single Author column with the full name by which they are generally known.
I've deployed a library catalog app to a non-profit. I didn't cater for this approach to author names. In hindsight, it could have been a better approach, albeit it would add to the complexity of the task to have to do contains searches on Author names to find variations. That might get more problematic if the number of volumes in the library gets very large.
If I deploy a second version to another group with which I'm discussing the proposal, I will have to give serious thought to this approach.
In the early days of spell checking we fed all our names in the family into a document to see what it came up with. My wife Fiona Sheridan was changed to Viola shredding!
In the early days of spell checking we fed all our names in the family into a document to see what it came up with. My wife Fiona Sheridan was changed to Viola shredding!
I apologize for not responding earlier, we had issues at work with the building and the chaos meant I didn't have a chance to really focus on this project, it's more of a side project anyway.
I just realized this was a UK specific Access programmers forum, but everyone seems so incredibly helpful. We use the first and last name (Visually) that is listed on the Veteran's documentation from our VA, or what is specifically listed on the DD-214, we do have a block in the table for middle names and Jr, Sr, III, IV and so on. At this time we are a low population town, with less than 100 veterans using this Veteran Relief Fund.
I think right this minute, I would like to get the First and Last name to populate based on what is selected above, I can type into the First and Last name blocks and it all saves into the table correctly. We will be having volunteers fill this out in the future, to record the Vouchers, but this is just to keep track of who is using them and how often. They are limited to twice per month with a maximum total dollar amount they can use in the future.
I believe maybe I need to have the Row Source filled in for the Last Name and First Name based on the VeteranID? I feel like I am very close to getting this working correctly, and then work on a new database built correctly.
Actually, AWF is UK-based, but by no means UK specific. We all try to be aware of the many international issues, such as date formatting, that everyone confronts everywhere in the world.
What matters primarily is the work flow you are implementing for the specific context in which it will be used. That determines what you need to do in YOUR database.
If the only values required for your workflow are First and Last Names, then by all means, capture them. I suspect, though, that it would be wise to allow for all 4 fields, just in case: FirstName, MiddleName, LastName, Suffix. Sooner or later, even with a couple hundred people, you'll need them. You can concatenate them into a query that provides a Row Source for a listbox or combobox.
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 form from one of my demo files illustrated below:
In the customer combo box you see the customer's name in LastName, FirstName format, but the combo box is bound to a numeric ContactID column in the form's Orders table. The RowSource property of the combo box is:
SQL:
SELECT
Contacts.ContactID,
[LastName] & ", " & [FirstName] AS Customer,
Contacts.Address,
Cities.City,
Regions.Region,
Countries.Country
FROM
Countries
INNER JOIN (
Regions
INNER JOIN (
Cities
INNER JOIN Contacts ON Cities.CityID = Contacts.CityID
) ON Regions.RegionID = Cities.RegionID
) ON Countries.CountryID = Regions.CountryID
ORDER BY
Contacts.LastName,
Contacts.FirstName;
The combo box's ColumnCount property is 6 and its ColumnWidths property is 0cm;3cm;4cm;3cm;3cm;3cm. By setting the dimension of the first column to 0cm this hides the ContactID column, so the second column's contents, i.e the customer's concatenated name, shows in the control after a selection is made.
The other columns, the customer's address, also show in the control's drop down list. To show these in the form the ControlSource properties of the unbound text boxes below the customer name reference the Column property of the combo box as follows:
The Column property is zero-based, so Column(2) is the third column, Column(3) the fourth, and so on.
The only situation in which you would want to insert values from columns in the selected row in the combo box would be if, in the referenced table, those values might change over time, but in the referencing table on which the form is based, you would want the values to remain static as those current at the time the record is entered into the referencing table. You'll find an example of this in the Items Ordered subform in the above form. In the subform the Item combo box's RowSource property is:
SQL:
SELECT
Items.ItemID,
Items.UnitPrice,
Items.Item
FROM
Items
ORDER BY
Items.Item;
The unit price of items will of course change over time, but the unit prices in each order should remain static as those current when the order was made. So, in this context the OrderDetails table also contains a UnitPrice column, and, in the AfterUpdate event procedure of the combo box the current unit price is assigned to that column as follows:
Code:
Private Sub ItemID_AfterUpdate()
' assign current unit price from Items table
' to UnitPrice column in OrderDetails table
Me.UnitPrice = Me.ItemID.Column(1)
End Sub
So, whether you reference the FirstName and LastName values from the veterans table, as in the first example above, or whether you assign it to a column in the form's table, as in the second example above, will depend on whether the names can change over time, and if so, whether you would want the original name at the time data is entered via the form to remain static in the form's table, or you would want those changes to be reflected in the form when opened in the future by being referenced and shown in unbound controls.
I apologize for not responding earlier, we had issues at work with the building and the chaos meant I didn't have a chance to really focus on this project, it's more of a side project anyway.
I just realized this was a UK specific Access programmers forum, but everyone seems so incredibly helpful. We use the first and last name (Visually) that is listed on the Veteran's documentation from our VA, or what is specifically listed on the DD-214, we do have a block in the table for middle names and Jr, Sr, III, IV and so on. At this time we are a low population town, with less than 100 veterans using this Veteran Relief Fund.
I think right this minute, I would like to get the First and Last name to populate based on what is selected above, I can type into the First and Last name blocks and it all saves into the table correctly. We will be having volunteers fill this out in the future, to record the Vouchers, but this is just to keep track of who is using them and how often. They are limited to twice per month with a maximum total dollar amount they can use in the future.
I believe maybe I need to have the Row Source filled in for the Last Name and First Name based on the VeteranID? I feel like I am very close to getting this working correctly, and then work on a new database built correctly.
With little information about your voucher system, but using a little common sense and logic, you might be able to sue a design something like this:
I didn't know if there is more than one issuer, but if there isn't, then just delete TblIssuer and its foreign keys (IssuerID) in TblIsssuerContact and TblVoucherType. I just took a guess at the rest.
