Replies

Because I’d like to retain the current value of A1 in B1 (say, as a basecase), while continuing to allow column A to be updated as the underlying data change.

As I remarked in my first post, I can do this with paste special/values. The disadvantage of this is that while it will place the value in B1, it will not leave any notation there about where that number came from. If there is a function that will do this, and show that B1 was at some point derived from A1, it would be relatively obvious that the value retained in B1 was a base case from some earlier run.

I’m envisioning something like this:

1) I have a formula in column A, toggled so values are showing.
2) I would like to be able to go to B1 and type a function, called (say) valueCopy(A1) that will copy the value shown for the formula in A1 only over to B1.

Does such a thing exist?

I’m envisioning something like this:

1) I have a formula in column A, toggled so values are showing.
2) I would like to be able to go to B1 and type a function, called (say) valueCopy(A1) that will copy the value shown for the formula in A1 only over to B1.

Does such a thing exist?

Reply to Gary’s “One Other Item” post: there are a lot of separate questions here so I cut and pasted where appropriate.

1) “what happens if you have more then one person with the same name” – the current version of the database will fail when that happens, but I am aware of that problem and was putting it off until I understood more about junction tables.
2) what happens if you have more then one person with the same … last name” – I haven’t been able to get the current set up to fail in that circumstance. If you can, I’d like to know about it.
3) “Table one stores First name and is linked to table two which stores last names (for this example) as a one to many relationship. However, this will also create problems on people with the same name as how will you know which one they are? Is this what you are using the junction table for, identifying unique id’s for common first and last names (which will also cause problems).” – Yes, that is what the junction table is for. I don’t think there is a problem until I get to a common first and last name, which as I remarked above, is a problem I will address down the road (there are additional fields I can use).
4) “Typically, in a database that is storing key data such as a persons name, an ID is created that identifies each person as a unique entity.” – yes, I do get into the non-unique entity issue here, but as I said in the previous post, that is a lark. I could’ve used full and distinct names for entities, and then had a junction table to put them into pairs, but it would have been more cumbersome.
5) “In either event if you use one or two tables to store the data in this example of names, it would be easier to drive the combo boxes from these tables then the junction table.” – this is something that I am already comfortable with.

I think the bottom line for all the posts, and particularly for these last two, is that I’m really undertaking an exercise to learn more about junction tables. First and last names was just what I thought might be a natural (and shorthand) way to do that. If it would help, I can redo the sample DB I posted with full names of individuals/entities in each of the two tables, and then a junction table to form pairs of them.

Reply to Gary’s “One Other Item” post: there are a lot of separate questions here so I cut and pasted where appropriate.

1) “what happens if you have more then one person with the same name” – the current version of the database will fail when that happens, but I am aware of that problem and was putting it off until I understood more about junction tables.
2) what happens if you have more then one person with the same … last name” – I haven’t been able to get the current set up to fail in that circumstance. If you can, I’d like to know about it.
3) “Table one stores First name and is linked to table two which stores last names (for this example) as a one to many relationship. However, this will also create problems on people with the same name as how will you know which one they are? Is this what you are using the junction table for, identifying unique id’s for common first and last names (which will also cause problems).” – Yes, that is what the junction table is for. I don’t think there is a problem until I get to a common first and last name, which as I remarked above, is a problem I will address down the road (there are additional fields I can use).
4) “Typically, in a database that is storing key data such as a persons name, an ID is created that identifies each person as a unique entity.” – yes, I do get into the non-unique entity issue here, but as I said in the previous post, that is a lark. I could’ve used full and distinct names for entities, and then had a junction table to put them into pairs, but it would have been more cumbersome.
5) “In either event if you use one or two tables to store the data in this example of names, it would be easier to drive the combo boxes from these tables then the junction table.” – this is something that I am already comfortable with.

I think the bottom line for all the posts, and particularly for these last two, is that I’m really undertaking an exercise to learn more about junction tables. First and last names was just what I thought might be a natural (and shorthand) way to do that. If it would help, I can redo the sample DB I posted with full names of individuals/entities in each of the two tables, and then a junction table to form pairs of them.

Gary:

The purpose of the junction table is that I am really trying to figure out all I can about how junction tables work. So, I set up (what I thought) was a simple example where I would understand what I wanted the data to do before I started working on it in Access.

Let me say first that the issue of first names in one table and last names in the other is a lark. I did that because I have an outstanding (low priority) database problem that I need to sort through involving teams of different individuals, grouped in different ways, where the order of the names matters. Rather than working with pairs of individuals’s full names, I did it with first and last names – which was less cumbersome to write out. The set of names in the tables might suggest that there is a one-to-one relationship, but that is only because I shortened that list considerably before posting it.

So, I have a table of first names with a one-to-many relationship with a junction table of full names (each first name may belong to more than one person), and a table of last names with a one-to-many relationship with a junction table of full names (each last name may belong to more than one person).

So, I’m not sure if your suggestion helps me here (to repeat “I would suggest, for this example, making a one to many relationship between the tables if people with multiple last names can have the same first name. I.e., Items in table1 can be related to many items in table2”). I have set up my data intentionally so that there are some last names that are repeated with different first names, and some first names that are repeated with different last names.

Gary:

The purpose of the junction table is that I am really trying to figure out all I can about how junction tables work. So, I set up (what I thought) was a simple example where I would understand what I wanted the data to do before I started working on it in Access.

Let me say first that the issue of first names in one table and last names in the other is a lark. I did that because I have an outstanding (low priority) database problem that I need to sort through involving teams of different individuals, grouped in different ways, where the order of the names matters. Rather than working with pairs of individuals’s full names, I did it with first and last names – which was less cumbersome to write out. The set of names in the tables might suggest that there is a one-to-one relationship, but that is only because I shortened that list considerably before posting it.

So, I have a table of first names with a one-to-many relationship with a junction table of full names (each first name may belong to more than one person), and a table of last names with a one-to-many relationship with a junction table of full names (each last name may belong to more than one person).

So, I’m not sure if your suggestion helps me here (to repeat “I would suggest, for this example, making a one to many relationship between the tables if people with multiple last names can have the same first name. I.e., Items in table1 can be related to many items in table2”). I have set up my data intentionally so that there are some last names that are repeated with different first names, and some first names that are repeated with different last names.

I’m learning a lot of stuff from this thread, but I’m not sure if I am getting clearer on (what I thought) the core issue was.

Could you explain the oddness of the setup? Is it the division into first and last names? Is it the desire to limit first names to those that match up with the last names? This is apparently one of the things I am having trouble picking up from texts.

I also had trouble getting the requery to work in the onCurrent event. What should the syntax of that be?

I’m learning a lot of stuff from this thread, but I’m not sure if I am getting clearer on (what I thought) the core issue was.

Could you explain the oddness of the setup? Is it the division into first and last names? Is it the desire to limit first names to those that match up with the last names? This is apparently one of the things I am having trouble picking up from texts.

I also had trouble getting the requery to work in the onCurrent event. What should the syntax of that be?

That’s OK. I was just getting nervous as my thread dropped further down onto the second page.

Hans’ solution is raising new issues. I will respond to his post in a few mintues.

That’s OK. I was just getting nervous as my thread dropped further down onto the second page.

Hans’ solution is raising new issues. I will respond to his post in a few mintues.

A stripped down database is attached. I hope this helps.

I’ve been designing (bad) databases on and off for about 8 years, and I am trying to unlearn all my spreadsheet-like habits. I know some VBA (and more VB) but am having trouble applying it to Access. I am open to all suggestions about how to improve my database design, or to links that can help me with VBA.

I’m just using names as a straightforward example to figure out some design issues involving junction tables (which I more or less used to keep in a spreadsheet form). So, I am not married to this set of names in any way.

The DB contains 3 tables: 1) tblFirstNames (containing an autonumber primary key called idFirstName and the text field firstName), 2) tblLastNames (containing an autonumber primary key called idLastName and the text field lastName), and 3) a junction table called tblFullName (containing an autonumber primary key called idFullName and foreign keys idFirstName and idLastName). There is a query showing the text equivalent of the junction table. There are three forms: 1) one to get and set in tblFirstNames, 2) one to get and set in tblLastNames, and 3) one to add entries to tblFullNames.

The DB has one tweak: a suggestion from HansV from about two weeks ago about how to allow a form that is updating a junction table to add those new records to the underlying table too (i.e., frmFullNames will update tblFullNames and/or tblFirstNames and tblLastNames if a new name is entered in frmFullNames.

What I would like to figure out how to do is if I enter the name Smith in my cboLastNames in frmFullNames have cboFirstNames populated only with the names from tblFirstNames that match up with the last name Smith in tblFullNames.

A stripped down database is attached. I hope this helps.

I’ve been designing (bad) databases on and off for about 8 years, and I am trying to unlearn all my spreadsheet-like habits. I know some VBA (and more VB) but am having trouble applying it to Access. I am open to all suggestions about how to improve my database design, or to links that can help me with VBA.

I’m just using names as a straightforward example to figure out some design issues involving junction tables (which I more or less used to keep in a spreadsheet form). So, I am not married to this set of names in any way.

The DB contains 3 tables: 1) tblFirstNames (containing an autonumber primary key called idFirstName and the text field firstName), 2) tblLastNames (containing an autonumber primary key called idLastName and the text field lastName), and 3) a junction table called tblFullName (containing an autonumber primary key called idFullName and foreign keys idFirstName and idLastName). There is a query showing the text equivalent of the junction table. There are three forms: 1) one to get and set in tblFirstNames, 2) one to get and set in tblLastNames, and 3) one to add entries to tblFullNames.

The DB has one tweak: a suggestion from HansV from about two weeks ago about how to allow a form that is updating a junction table to add those new records to the underlying table too (i.e., frmFullNames will update tblFullNames and/or tblFirstNames and tblLastNames if a new name is entered in frmFullNames.

What I would like to figure out how to do is if I enter the name Smith in my cboLastNames in frmFullNames have cboFirstNames populated only with the names from tblFirstNames that match up with the last name Smith in tblFullNames.

I’m wondering if my last reply has been waiting too long, and whether I should repost.

Anybody out there following this thread?

Also, what should be my protocol on this site – should I contact the two people who commented with answers directly, or should I wait here for another response?

I’m wondering if my last reply has been waiting too long, and whether I should repost.

Anybody out there following this thread?

Also, what should be my protocol on this site – should I contact the two people who commented with answers directly, or should I wait here for another response?