Autofill a field in one table based on another table

[Guest]

I am creating a database that lists all furniture in a building. Several
reports are run based on the information entered on each item of furniture
input by the user. In order to save time entering the building name on each
report, I created a seperate table to type in all project information (this
way I can add a field to the report that changes based on what is entered in
the table instead of the user having to edit each report individually).
Everything was going great. I didn't have any relationships setup between the
two since there were really no related fields.

Then I realized that we needed a freight allowance, which is a percentage
that is tacked on to the grand total. At first I added it to the Item table,
but we didn't want anyone to change the percentage of one item and not all
items. It was supposed to be one percentage for the entire project. I decided
to add it to the Project table.

This is when the problem started. It actually works fine for most
applications. I created a query that just pulled all fields from the Project
table and all items from the Item table. I then have other queries that use
that query as it's base.

Then there is the form. Originally, I was pulling data for the form from a
query [Item Details] that got information from the Item table, plus added new
calculation fields. That query has some other relationships to a Fabric
table. So I created another query [Item Detail and Project Info] that pulls
all fields from Item Detail query and the Project Info Table.

When I tried changing the data source of the form from the [Item Details]
query to the [Item Detail and Project Info] query, the information in the
form was not editable. I found that it was because there was no relationship
between the two information sources in the query.

So I decided I had to create a relationship between the two tables. I
created a [ProjectNumber] field in the Item table. Okay, great, but now the
user has to add the project number to each item, but generally every item in
the database is linked to the same project.

I did create a one to many relationship with "enforce referential integrety"
checked and "cascade update" checked, but how can I force each item to insert
the project number by default?

 

[Amy Blankenship]

I am creating a database that lists all furniture in a building. Several
reports are run based on the information entered on each item of furniture
input by the user. In order to save time entering the building name on
each
report, I created a seperate table to type in all project information
(this
way I can add a field to the report that changes based on what is entered
in
the table instead of the user having to edit each report individually).
Everything was going great. I didn't have any relationships setup between
the
two since there were really no related fields.

Then I realized that we needed a freight allowance, which is a percentage
that is tacked on to the grand total. At first I added it to the Item
table,
but we didn't want anyone to change the percentage of one item and not all
items. It was supposed to be one percentage for the entire project. I
decided
to add it to the Project table.

This is when the problem started. It actually works fine for most
applications. I created a query that just pulled all fields from the
Project
table and all items from the Item table. I then have other queries that
use
that query as it's base.

Then there is the form. Originally, I was pulling data for the form from a
query [Item Details] that got information from the Item table, plus added
new
calculation fields. That query has some other relationships to a Fabric
table. So I created another query [Item Detail and Project Info] that
pulls
all fields from Item Detail query and the Project Info Table.

When I tried changing the data source of the form from the [Item Details]
query to the [Item Detail and Project Info] query, the information in the
form was not editable. I found that it was because there was no
relationship
between the two information sources in the query.

So I decided I had to create a relationship between the two tables. I
created a [ProjectNumber] field in the Item table. Okay, great, but now
the
user has to add the project number to each item, but generally every item
in
the database is linked to the same project.

I did create a one to many relationship with "enforce referential
integrety"
checked and "cascade update" checked, but how can I force each item to
insert
the project number by default?

UPDATE ITEM Set [ProjectNumber] = 1 WHERE [ProjectNumber] IS Null

Set 1 as the default value of that field on the form or the table.

HTH;

Amy

 

[Guest]

Okay, I guess I'm a novice. So do I put this as an event on the form or in a
query somehow?

Instead of updating all of the [ProjectNumber] fields to "1" can I use a
field in another table to udate them?

How about
UPDATE ITEM Set [ProjectNumber] = [Project Info].[Project Number] WHERE
[ProjectNumber] IS Null

Basically, I always want
[Item].[Project Number] to equal [Project Info].[Project Number]

 

[John W. Vinson]

Basically, I always want
[Item].[Project Number] to equal [Project Info].[Project Number]

If there are 841 items in [Item] and 41 projects in [Project Info]... what do
you mean by "always equal"? Which one of the 41 projects should each Item be
set equal TO???

John W. Vinson [MVP]

 

[Guest]

I guess that's the interesting part. There is always only one project per
database so only the first field is actually used.

Maybe there is a better way to share project information throughout the
database. For example, I have to define the following, which will only occur
once per database, for the entire project, but might change during the course
of the project (i.e. Project Name changes slightly, Project Manager quits,
the boss says "hey, what happens if we charge 9% instead of 8% for freight)
-Project Name
-Project Number
-Freight Allowance
-Mark Up Rate
-Project Manager

Basically, I always want
[Item].[Project Number] to equal [Project Info].[Project Number]

If there are 841 items in [Item] and 41 projects in [Project Info]... what do
you mean by "always equal"? Which one of the 41 projects should each Item be
set equal TO???

John W. Vinson [MVP]

 

[John W. Vinson]

I guess that's the interesting part. There is always only one project per
database so only the first field is actually used.

Ah. Only the first RECORD then - not field. (The first field is presumably
Project Name).

If that's the case then I'd just use DLookUp to look up each field in the
one-record table.

I *REALLY* have to question this design, though. It sounds like you're storing
very similar data in a whole multitude of separate databases. Unless each
database is over a billion bytes in size - or unless the projects have
absolutely nothing to do with each other and none of the other tables have
information in common - I'd really wonder whether you might be better off with
everyone sharing one big database with all the projects!

Surely each database has (say) a table of employees, or a table of shippers,
or a table of states, or the like? Are you storing this information
redundantly in every database? Who's the poor person who has to keep them all
in synch!?

John W. Vinson [MVP]

 

[Guest]

So I use a lookup in the Item table to lookup the first record in the
ProjectName table, right? I then got the idea from the first post that I
could then have the default value of "1" and each new record would then have
the correct project number, but when I tried this, it actually returned "1"
instead of the appropriate project number.

As for the design... each must be independant. Each project is independant
from the other and must be kept seperate. Actually, there are no names or
shippers or anything else... at least not so far. Generally a purchasing
company takes over the actual purchasing after the design is complete so
there are no invoices, no tracking of shipments, linking employees or
schedules. Nothing. Basically only budgetting of how much furniture is in the
project... formatted several different ways.

Even the furniture is generally custom for each job. Finishes and/or fabric
is different for each project even if product numbers might be the same, but
we have to keep track of each project individually for historical purposes
(you know... in case the data police - or lawyers - come by later).

 

[John W. Vinson]

So I use a lookup in the Item table to lookup the first record in the
ProjectName table, right?

NO.

See http://www.mvps.org/access/lookupfields.htm for a critique of this
misfeature.

Instead, use the built-in VBA function DLookUp() in the control source of at
textbox on the Form that you should be using for all interactions with your
data. Table datasheets aren't of much use for anything other than debugging -
use Forms!

I then got the idea from the first post that I
could then have the default value of "1" and each new record would then have
the correct project number, but when I tried this, it actually returned "1"
instead of the appropriate project number.

Now I'm COMPLETELY lost. There's only one project in the database - what's the
project number!?

As for the design... each must be independant. Each project is independant
from the other and must be kept seperate. Actually, there are no names or
shippers or anything else... at least not so far. Generally a purchasing
company takes over the actual purchasing after the design is complete so
there are no invoices, no tracking of shipments, linking employees or
schedules. Nothing. Basically only budgetting of how much furniture is in the
project... formatted several different ways.

Even the furniture is generally custom for each job. Finishes and/or fabric
is different for each project even if product numbers might be the same, but
we have to keep track of each project individually for historical purposes
(you know... in case the data police - or lawyers - come by later).

That's a VERY strong argument for keeping all the projects IN ONE DATABASE,
properly designed with referential integrity. Doing so lets you keep track of
all the projects within the database, rather than having to search through
dozens or hundreds of confusingly named .mdb files sitting on your disk.

John W. Vinson [MVP]

 

[Amy Blankenship]

NO.

See http://www.mvps.org/access/lookupfields.htm for a critique of this
misfeature.

Instead, use the built-in VBA function DLookUp() in the control source of
at
textbox on the Form that you should be using for all interactions with
your
data. Table datasheets aren't of much use for anything other than
debugging -
use Forms!


Now I'm COMPLETELY lost. There's only one project in the database - what's
the
project number!?

I agree. If the ProjectNumber is an AutoNumber ID field, then it would
pretty much usually be 1 unless you add and delete a lot of projects before
you start. But if it's not 1 then just use whatever you think the
appropriate project number would be.

That's a VERY strong argument for keeping all the projects IN ONE
DATABASE,
properly designed with referential integrity. Doing so lets you keep track
of
all the projects within the database, rather than having to search through
dozens or hundreds of confusingly named .mdb files sitting on your disk.

It's also a good argument for having a ProjectDetails table that keeps track
of changes in the project, such as changing its name or a changing manager,
over the course of time. Additionally, having one database means you can
also track your project managers across projects.

-Amy

 

[Pat Hartman \(MVP\)]

Banks are required to keep each account separate. Do you think they use a
separate database for each customer? or even a separate table? No. It is
quite easy to segregate data by properly coding the entries and then by
using queries to extract sets of data. In fact, now that you have project
number in your items table, you could have data for multiple projects in the
same table (of course, you would need to modify your form so that the user
only has to enter a product number once) but there would be no problem
isolating the data for one project from that for another project.

In any event to solve your immediate problem you need to put the ACTUAL
project number value as the default value for the project number in the
items table. You don't even have to show the project number on the form.
Access will populate the field automatically when a new record is saved.
For existing records, YOU need to run an update query to place the correct
value in the project number field.

 

[Guest]

I appreciate everyone's input, but architecture is a bit different than a
bank. Projects have a begining and an end. At the end of a project everything
goes in a box (litterally) and is moved to storage awaiting litigation. Once
the tolling period passes, everything is shredded.

Having one big database would not only increase the complexity of the
database (I don't write databases for banks for a reason), but intermixing of
data is undesireable. Project numbers can not be "autonumbers" as they are
generated by the accounting software. Of course if we had a link to the
accounting software (which we don't have access to at our remote location) we
could select from a list, but this would require way too much red tape on the
corporate side.

Lila

 

[Guest]

So basically, there is no way to have the default value of a field be the
first item of another table, right? I'd have to manually type in the project
number in the field properties of the table as the default for the field?

 

[John W. Vinson]

So basically, there is no way to have the default value of a field be the
first item of another table, right? I'd have to manually type in the project
number in the field properties of the table as the default for the field?

I think that Pat was saying that it's a bad idea, not that it's impossible. It
is impossible to do so in a Table, but if you're entering data using a Form
you can set the DefaultValue property of a textbox to

=DLookUp("[ProjectID]", "[Tablename]")

If it's a one-row table you'll be ok.

John W. Vinson [MVP]

 

[Pat Hartman \(MVP\)]

Perhaps a bank wasn't a good analogy. How about a 747? Three
configurations are maintained for each aircraft - as planned (as it came off
the drafting table), as designed (as the production engineers intended to
build it), and as built (how it actually was built). All configurations for
all types of aircraft are stored in the same table. They all start with a
basic configuration for each aircraft and then modifications are applied.

I should have been more precise in my answer. You can't do what you are
asking in the table itself but as John mentioned, you can do it with
DLookup() in a form field.