update records in a table

[Muneer Mikel]

How can I update records in a table from a value from another table (that has
only one record) that is not related to the first. I tried to relate the two
tables, however there is no common field between the tables!

 

[Rick B]

Well then how will you know which record to use from the second table to
update the first?

Rick B

 

[John Vinson]

On Fri, 11 Mar 2005 12:17:03 -0800, Muneer Mikel <Muneer

How can I update records in a table from a value from another table (that has
only one record) that is not related to the first. I tried to relate the two
tables, however there is no common field between the tables!

Since the second table has only one record, you could just update to

DLookUp("[fieldname]", "[secondtablename]")

John W. Vinson[MVP]

 

[Muneer Mikel]

Rick, John, thank you for your prompt response!

Rick.. the first table (Sessions) has one record which gets updated once a
year by entering the [SessionYear] field by a user. I need to update the
[SessionYear] field in the (Members) table without entering this value to
each and every record in the (Members) table.

John.. I used your code in the Defualt value property for the field I want
to update [SessionYear]. It didn't work in the table design, although it
worked in the form design. However, I don't need to show this value on the
form, I just need it when I'm creating a report at the end of the Session
Year. This report will show how many Members attanded that session.

I hope I made it clear

 

[Larry Daugherty]

Hi,

You can show the Session year on your form to verify that the correct year
is entered or you can make that control invisible on your form. The value
in that control will go into your table and be available for reports.

HTH
--
-Larry-
--

Rick, John, thank you for your prompt response!

Rick.. the first table (Sessions) has one record which gets updated once a
year by entering the [SessionYear] field by a user. I need to update the
[SessionYear] field in the (Members) table without entering this value to
each and every record in the (Members) table.

John.. I used your code in the Defualt value property for the field I want
to update [SessionYear]. It didn't work in the table design, although it
worked in the form design. However, I don't need to show this value on the
form, I just need it when I'm creating a report at the end of the Session
Year. This report will show how many Members attanded that session.

I hope I made it clear

How can I update records in a table from a value from another table (that has
only one record) that is not related to the first. I tried to relate the two
tables, however there is no common field between the tables!

 

[Muneer Mikel]

Hi Larry,

How will that value get saved into my table when I chose the DLookup
function to be in the control source property?!!

Thank you

Hi,

You can show the Session year on your form to verify that the correct year
is entered or you can make that control invisible on your form. The value
in that control will go into your table and be available for reports.

HTH
--
-Larry-
--

Rick, John, thank you for your prompt response!

Rick.. the first table (Sessions) has one record which gets updated once a
year by entering the [SessionYear] field by a user. I need to update the
[SessionYear] field in the (Members) table without entering this value to
each and every record in the (Members) table.

John.. I used your code in the Defualt value property for the field I want
to update [SessionYear]. It didn't work in the table design, although it
worked in the form design. However, I don't need to show this value on the
form, I just need it when I'm creating a report at the end of the Session
Year. This report will show how many Members attanded that session.

I hope I made it clear

How can I update records in a table from a value from another table (that has
only one record) that is not related to the first. I tried to relate the two
tables, however there is no common field between the tables!

 

[John Vinson]

How will that value get saved into my table when I chose the DLookup
function to be in the control source property?!!

Consider putting the DLookUp in the Default property instead.

John W. Vinson[MVP]

 

[Muneer Mikel]

Yes.. It finally worked, Thank you so much John. I appreciate your help

I have another one for you John, do you know of a code that will assign
table seats randomly for people attanding two events based on these
conditions:

1- All related persons in one family sit on the same table
2- People who sit on table (#65 for example) in event 1, shouldn't sit on
the same table in the event 2
3- People from the same city shouldn't sit together

I don't even know of a way to approch this!!

 

[John Vinson]

Yes.. It finally worked, Thank you so much John. I appreciate your help

I have another one for you John, do you know of a code that will assign
table seats randomly for people attanding two events based on these
conditions:

1- All related persons in one family sit on the same table
2- People who sit on table (#65 for example) in event 1, shouldn't sit on
the same table in the event 2
3- People from the same city shouldn't sit together

I don't even know of a way to approch this!!

Ph.D. theses have been written on the "satisfiability problem"...
usually with more than three constraints, but it's still a VERY
complex issue!

As stated, aren't 1 and 3 contradictory? I live in the same city as my
wife - so as family we must sit at the same table, and as citizens of
Parma we are not allowed to sit at the same table. For that matter, at
some events you might have fewer cities than tables - making 3 an
impossible constraint.

This would be an interesting challenge... I suspect that a self-join
query of some sort should work but I don't see any obvious solution
either!

John W. Vinson[MVP]

 

[Muneer Mikel]

The related family belongs to one record in the table, so if the following
record has a family, live in the same city, these two families shouldn't sit
together. The purpose for doing this is to make people from different cities
get to know each other, asuming the people from the same city know each other
already!

Thank you John for your help, I guess I have to do more searching

 

[John Vinson]

The related family belongs to one record in the table, so if the following
record has a family, live in the same city, these two families shouldn't sit
together. The purpose for doing this is to make people from different cities
get to know each other, asuming the people from the same city know each other
already!

I realized that's what you meant after I replied.

This will probably require some VBA code to deal with "backtracking" -
i.e. if putting the Jones family at Table1 and the Valdez family at
Table2 and the Roberts family at Table3 leaves no place for the
Jenkins... And when you throw in the "randomize" option it gets even
worse!

But here's an idea: it might be VERY VERY slow and might not work at
all, but consider a Cartesian Join query. If there are (say) eight
places at each table, join the Tables table to *eight instances* of
the Families table. These eight instance would not be joined to one
another. Instead, you'll have criteria on each one to exclude the City
value in the other seven. You'ld also have to join in the previous
events' tables to exclude people sitting at the same table as before.
This will give you all of the possible seating arrangements consistant
with the constraints (probably many millions of them). You'ld then
have to come up with some way to arbitrarily pick one of the possible
arrangements.

This may not be practical; it's an exponentially growing set as you
add seats at each table or tables, and if both numbers get large, you
may get vastly more "hits" than Access (or any program!) will be able
to handle.

John W. Vinson[MVP]

 

[Muneer Mikel]

Thank you John, I'll look more into your solution