merging tables into one

[Guest]

Hi,

I have 3 tables with all identical field names.
table 1 is called "tbl_fin"
table 2 is called "tbl_tbs"
table 3 is called "tbl_pshrmac"

I would like ALL the data in each of these tables to be merged together in
one table called "tbl_summary".

I have 3 forms that are going to be used for data entry that correspond to
the first 3 tables. I will be creating a fourth form for view/edit records
purposes that will be matched to "tbl_summary". I would like it to be that
when the user looks up a record using that form from "tbl_summary", the
changes are saved in "tbl_summary", but these changes are also then made in
the individual tables that the records were originally stored in.

Is this something that is possible, and if so, how can I go about doing this?

therefore the data flow from one table to the summary table should be such as
< - >, kind of like having an upload/download function. This way there is
never duplicates and data in tbl_fin for example matches all the records in
tbl_summary that come from tbl_fin. Hopes I am making sense....

Thanks,

Jean-Francois

 

[Guest]

Hi Jean-Francois,

This design, which involves three source tables and one summary table, in
not considered a normalized (proper) design. You should generally not have
the same data in more than one table.

My suggestion is to create a new table, but name it something other than
tbl_summary. Add a new field that allows you to track which type of data the
record represents, for example: fin, tbs or pshrmac. Have one form that pulls
data from this table (likely using a query based on this table) to edit all
of the records.

Here is a good paper on database design that I recommend that you download
and read. It is written by Michael Hernandez, author of Database Design for
Mere Mortals:

http://www.seattleaccess.org/
(See the last download titled "Understanding Normalization" in
the Meeting Downloads page)


Michael includes the following quote on page 23 of this document:

"The most important point for you to remember is that you will always
re-introduce data integrity problems when you de-Normalize your structures!
This means that it becomes incumbent upon you or the user to deal with this
issue. Either way, it imposes an unnecessary burden upon the both of you.
De-Normalization is one issue that you'll have to weigh and decide for
yourself whether the perceived benefits are worth the extra effort it will
take to maintain the database properly."
<End Quote>

There are also several DB design papers available here:

http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html#DatabaseDesign101

The short answer is that yes, you can do what you want to do, but you really
shouldn't be pursuing a non-normalized DB design. This will only give you
headaches in the future.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Guest]

Hi Tom,

I will explain why I need to have the 3 tables. Each table contains a
document tracking number such as F-000001.
This is coming from F = Finance
tbl_fin generates the autonumber F-000001,.....F-00000n
tbl_tbs generates the autonumber T-000001.....T-000000n
tbl_pshrmac generates the autonumber P-000001......P-00000n

I would like to have the summary table take all the records and bring it
into the one so that a user can search the full database and make edits to
the records.

I have tried many different ways to make this happen, and if it was not for
the unique way of hte autogenerated number needing to be then this would not
be an issue to have just one table. In fact I would prefer it this way.

Do you know of a way that if the user on the form were to select the option
for deparment = FIN, then the autogenerate number would be F-000001 and so
on.....and then if he chooses TBS then it would equal T-000001?

I have not been able to figure out such a way. The only way I found to
make what I need possible was to have the three tables, have the format of
the autonumber into each table such as the right letter for the correct
deparmtent, (table is named by department as you see)....and then having a
pre-selection form asking for the deparment, the user inputting this, and
based on the user input, a form comes up (there is a form for each table -
departments). So that is how I hae my database designed so far, and I do
understand its mroe work and woudl rather have it otherwise.

If you know of a way to get my autonumber or have a number generate
automatically based on the department selection and where I could have for
example my first record be recorded as F-000001 and have my second record
recorded as T-000001 if TBS was selected for the second record, then this
will make my database a lot simpler and easier to manage.

Thank you.

Let me know what you think.

 

[Guest]

Hi Jean-Francois,

I try to always avoid using the so-called "Intelligent" key, as you
describe, so I cannot say that I have a sample handy to give to you. However,
it would not be all that difficult to do with a little VBA coding. My
recommendation is to start with this sample, which you can download from
Access MVP Roger Carlson's site:

http://www.rogersaccesslibrary.com/download3.asp?SampleName=AutonumberProblem.mdb

You can add a criteria to the DMax function to select the maximum number
that corresponds to the department selected. If the finance department was
selected, then the criteria would include only those auto generated numbers
that include F as the first character (ie. Left$([FieldName], 1) = 'F'. Here
is an article that discusses the DLookup function, and includes examples of
using criteria:

DLookup Usage Samples
http://www.mvps.org/access/general/gen0018.htm

You'd want to add code that requires the user to select a department before
they attempt to save the record, so that the correct criteria could be
created on-the-fly.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Guest]

Hi Tom,

Thanks a lot for your help on this. One last question I might have
regarding this is how I should set the field in my table. Should I make this
a text field as it would contain both the F and the sequential number? Yes I
know...this probably sounds really stupid on my part and is probably self
explanatory...but I just want to make sure that using the number field that
only numbers will be able to go in there....

Thanks,

Jean-Francois

Hi Jean-Francois,

I try to always avoid using the so-called "Intelligent" key, as you
describe, so I cannot say that I have a sample handy to give to you. However,
it would not be all that difficult to do with a little VBA coding. My
recommendation is to start with this sample, which you can download from
Access MVP Roger Carlson's site:

http://www.rogersaccesslibrary.com/download3.asp?SampleName=AutonumberProblem.mdb

You can add a criteria to the DMax function to select the maximum number
that corresponds to the department selected. If the finance department was
selected, then the criteria would include only those auto generated numbers
that include F as the first character (ie. Left$([FieldName], 1) = 'F'. Here
is an article that discusses the DLookup function, and includes examples of
using criteria:

DLookup Usage Samples
http://www.mvps.org/access/general/gen0018.htm

You'd want to add code that requires the user to select a department before
they attempt to save the record, so that the correct criteria could be
created on-the-fly.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

Hi Tom,

I will explain why I need to have the 3 tables. Each table contains a
document tracking number such as F-000001.
This is coming from F = Finance
tbl_fin generates the autonumber F-000001,.....F-00000n
tbl_tbs generates the autonumber T-000001.....T-000000n
tbl_pshrmac generates the autonumber P-000001......P-00000n

I would like to have the summary table take all the records and bring it
into the one so that a user can search the full database and make edits to
the records.

I have tried many different ways to make this happen, and if it was not for
the unique way of hte autogenerated number needing to be then this would not
be an issue to have just one table. In fact I would prefer it this way.

Do you know of a way that if the user on the form were to select the option
for deparment = FIN, then the autogenerate number would be F-000001 and so
on.....and then if he chooses TBS then it would equal T-000001?

I have not been able to figure out such a way. The only way I found to
make what I need possible was to have the three tables, have the format of
the autonumber into each table such as the right letter for the correct
deparmtent, (table is named by department as you see)....and then having a
pre-selection form asking for the deparment, the user inputting this, and
based on the user input, a form comes up (there is a form for each table -
departments). So that is how I hae my database designed so far, and I do
understand its mroe work and woudl rather have it otherwise.

If you know of a way to get my autonumber or have a number generate
automatically based on the department selection and where I could have for
example my first record be recorded as F-000001 and have my second record
recorded as T-000001 if TBS was selected for the second record, then this
will make my database a lot simpler and easier to manage.

Thank you.

Let me know what you think.

 

[Guest]

Ok, so if I understand all of this properly, what I need to do is have a
table with the autonumber property field and the deparmtnet field. Both of
these would be text.

So I would have a table called "tbl_docinput"
fields are "DeptID" which would be a lookup of the "Department" table and
would look up the "DeptID" field in this table, giving me choices of "FIN",
"TSB" and "PSHRMAC".

Then I would create a form "frm_input" where the deparment "DeptID" choice
would be first and then the autonumber "DocTrack#" would come next.

I would need to write a code to do a where:

DeptID = dlookup("DeptID", "tbl_docinput", "criteria=
"'&Forms!frm_input!"DeptID"&""')

Dept = left$(DeptID, 1)

This would give me the first letter of whatever selection I made in the
DeptID field on the form. Correct?

Now I somehow need to have a field where I would concatenate this Dept with
a - and a bunch of 000000s to make the "F-000000" for my field. Lets called
this Dselsequential

DSequential=("DocTrack#", "tbl_input") +1

Am I missing anything, is this the right approach, and if so, where would I
make/enter this code?

Thank you for your help on this.

Sincerely,

Hi Jean-Francois,

I try to always avoid using the so-called "Intelligent" key, as you
describe, so I cannot say that I have a sample handy to give to you. However,
it would not be all that difficult to do with a little VBA coding. My
recommendation is to start with this sample, which you can download from
Access MVP Roger Carlson's site:

http://www.rogersaccesslibrary.com/download3.asp?SampleName=AutonumberProblem.mdb

You can add a criteria to the DMax function to select the maximum number
that corresponds to the department selected. If the finance department was
selected, then the criteria would include only those auto generated numbers
that include F as the first character (ie. Left$([FieldName], 1) = 'F'. Here
is an article that discusses the DLookup function, and includes examples of
using criteria:

DLookup Usage Samples
http://www.mvps.org/access/general/gen0018.htm

You'd want to add code that requires the user to select a department before
they attempt to save the record, so that the correct criteria could be
created on-the-fly.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

Hi Tom,

I will explain why I need to have the 3 tables. Each table contains a
document tracking number such as F-000001.
This is coming from F = Finance
tbl_fin generates the autonumber F-000001,.....F-00000n
tbl_tbs generates the autonumber T-000001.....T-000000n
tbl_pshrmac generates the autonumber P-000001......P-00000n

I would like to have the summary table take all the records and bring it
into the one so that a user can search the full database and make edits to
the records.

I have tried many different ways to make this happen, and if it was not for
the unique way of hte autogenerated number needing to be then this would not
be an issue to have just one table. In fact I would prefer it this way.

Do you know of a way that if the user on the form were to select the option
for deparment = FIN, then the autogenerate number would be F-000001 and so
on.....and then if he chooses TBS then it would equal T-000001?

I have not been able to figure out such a way. The only way I found to
make what I need possible was to have the three tables, have the format of
the autonumber into each table such as the right letter for the correct
deparmtent, (table is named by department as you see)....and then having a
pre-selection form asking for the deparment, the user inputting this, and
based on the user input, a form comes up (there is a form for each table -
departments). So that is how I hae my database designed so far, and I do
understand its mroe work and woudl rather have it otherwise.

If you know of a way to get my autonumber or have a number generate
automatically based on the department selection and where I could have for
example my first record be recorded as F-000001 and have my second record
recorded as T-000001 if TBS was selected for the second record, then this
will make my database a lot simpler and easier to manage.

Thank you.

Let me know what you think.

 

[Guest]

Hi Jean-Francois,

I think I would store the data in two fields: a text field for the letter
designation, and a number / long integer for the numeric portion. Having the
numeric portion as a number will greatly simplify the process of incrementing
this value. You can create a multi-field unique index on these two fields to
prevent any duplicates from being entered. When displaying the document
tracking number on forms and reports, just concatenate the values at run time
in a textbox that is bound to an expression. Something like this:

=[FieldName1] + [FieldName2]

where FieldName1 is the name of the text field that stores the first
character and FieldName2 is the name of the numeric field that stores the
numeric portion of the document tracking number.

You could actually store the entire description in FieldName1, for example:

FieldName1
FIN
TBS
Pshrmac

and then have an expression like this:

=Left([FieldName1], 1) + [FieldName2]


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Guest]

Ok, so if I understand all of this properly, what I need to do is have a

table with the autonumber property field and the department field. Both of
these would be text.

No. First, I would stay away from the autonumber data type when trying to
create the "Intelligent Key". You want to use a number / long integer
instead. This way, you will not "consume" autonumber values if one starts to
add a record, but then decides to press the Escape key to back out of the
process, before actually saving the new record. The department field should
be text, but the numeric portion should be stored in a field set as a number
data type.

Then I would create a form "frm_input" where the deparment "DeptID" choice
would be first and then the autonumber "DocTrack#" would come next.

Just change autonumber to number in the sentence shown above.

I would need to write a code to do a where:

DeptID = dlookup("DeptID", "tbl_docinput", "criteria=
"'&Forms!frm_input!"DeptID"&""')

I believe you are going to need to use DMax, not DLookup, and you'll need to
use a compound criteria to select the appropriate record based on two fields.
Getting a compound criteria to work with a domain aggregrate function can be
a bit tricky, but it is very do-able.

Now I somehow need to have a field where I would concatenate this Dept with
a - and a bunch of 000000s to make the "F-000000" for my field. Lets called
this Dselsequential

Concatenating a hyphen at run-time in a calculated control is no problem at
all. You can apply a format to display the leading zeros, without actually
storing them. For example, a format of: 000000 will display leading zeros
for a numeric field. A new form based on a table that includes a field with
this format will inherit the format for a textbox on the form. You'll need to
add this format to an existing textbox on a form if you set this format after
initially creating the form.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

Ok, so if I understand all of this properly, what I need to do is have a
table with the autonumber property field and the deparmtnet field. Both of
these would be text.

So I would have a table called "tbl_docinput"
fields are "DeptID" which would be a lookup of the "Department" table and
would look up the "DeptID" field in this table, giving me choices of "FIN",
"TSB" and "PSHRMAC".

Then I would create a form "frm_input" where the deparment "DeptID" choice
would be first and then the autonumber "DocTrack#" would come next.

I would need to write a code to do a where:

DeptID = dlookup("DeptID", "tbl_docinput", "criteria=
"'&Forms!frm_input!"DeptID"&""')

Dept = left$(DeptID, 1)

This would give me the first letter of whatever selection I made in the
DeptID field on the form. Correct?

Now I somehow need to have a field where I would concatenate this Dept with
a - and a bunch of 000000s to make the "F-000000" for my field. Lets called
this Dselsequential

DSequential=("DocTrack#", "tbl_input") +1

Am I missing anything, is this the right approach, and if so, where would I
make/enter this code?

Thank you for your help on this.

Sincerely,

Hi Jean-Francois,

I try to always avoid using the so-called "Intelligent" key, as you
describe, so I cannot say that I have a sample handy to give to you. However,
it would not be all that difficult to do with a little VBA coding. My
recommendation is to start with this sample, which you can download from
Access MVP Roger Carlson's site:

http://www.rogersaccesslibrary.com/download3.asp?SampleName=AutonumberProblem.mdb

You can add a criteria to the DMax function to select the maximum number
that corresponds to the department selected. If the finance department was
selected, then the criteria would include only those auto generated numbers
that include F as the first character (ie. Left$([FieldName], 1) = 'F'. Here
is an article that discusses the DLookup function, and includes examples of
using criteria:

DLookup Usage Samples
http://www.mvps.org/access/general/gen0018.htm

You'd want to add code that requires the user to select a department before
they attempt to save the record, so that the correct criteria could be
created on-the-fly.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

Hi Tom,

I will explain why I need to have the 3 tables. Each table contains a
document tracking number such as F-000001.
This is coming from F = Finance
tbl_fin generates the autonumber F-000001,.....F-00000n
tbl_tbs generates the autonumber T-000001.....T-000000n
tbl_pshrmac generates the autonumber P-000001......P-00000n

I would like to have the summary table take all the records and bring it
into the one so that a user can search the full database and make edits to
the records.

I have tried many different ways to make this happen, and if it was not for
the unique way of hte autogenerated number needing to be then this would not
be an issue to have just one table. In fact I would prefer it this way.

Do you know of a way that if the user on the form were to select the option
for deparment = FIN, then the autogenerate number would be F-000001 and so
on.....and then if he chooses TBS then it would equal T-000001?

I have not been able to figure out such a way. The only way I found to
make what I need possible was to have the three tables, have the format of
the autonumber into each table such as the right letter for the correct
deparmtent, (table is named by department as you see)....and then having a
pre-selection form asking for the deparment, the user inputting this, and
based on the user input, a form comes up (there is a form for each table -
departments). So that is how I hae my database designed so far, and I do
understand its mroe work and woudl rather have it otherwise.

If you know of a way to get my autonumber or have a number generate
automatically based on the department selection and where I could have for
example my first record be recorded as F-000001 and have my second record
recorded as T-000001 if TBS was selected for the second record, then this
will make my database a lot simpler and easier to manage.

Thank you.

Let me know what you think.

 

[Guest]

Hi Tom,

Thank you, that is very helpful. My question at this point is that would I
be able to store the full number as is in my table, such as F-000001.
Second question, would the number increment such as F-000001,
F-000002....F-00000n......however if "TBS" were chosen, would the number fall
back to T-000001 rather then go to
T-000034 if the first 33 records were "FIN"? Hope this makes sense. Let
me know what you think as that is the goal of the whole excercise.

Thank you very much again for your great advice! I really really appreciate
all your time and effort on this.

Sincerely,

Hi Jean-Francois,

I think I would store the data in two fields: a text field for the letter
designation, and a number / long integer for the numeric portion. Having the
numeric portion as a number will greatly simplify the process of incrementing
this value. You can create a multi-field unique index on these two fields to
prevent any duplicates from being entered. When displaying the document
tracking number on forms and reports, just concatenate the values at run time
in a textbox that is bound to an expression. Something like this:

=[FieldName1] + [FieldName2]

where FieldName1 is the name of the text field that stores the first
character and FieldName2 is the name of the numeric field that stores the
numeric portion of the document tracking number.

You could actually store the entire description in FieldName1, for example:

FieldName1
FIN
TBS
Pshrmac

and then have an expression like this:

=Left([FieldName1], 1) + [FieldName2]


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

Hi Tom,

Thanks a lot for your help on this. One last question I might have
regarding this is how I should set the field in my table. Should I make this
a text field as it would contain both the F and the sequential number? Yes I
know...this probably sounds really stupid on my part and is probably self
explanatory...but I just want to make sure that using the number field that
only numbers will be able to go in there....

Thanks,

Jean-Francois

 

[Guest]

Hi Jean-Francois,

My question at this point is that would I
be able to store the full number as is in my table, such as F-000001.

I've actually been spending some time today working up a sample for you. In
the example that I have ready now, the department and the document number are
stored in two fields.

Second question, would the number increment such as F-000001,
F-000002....F-00000n......however if "TBS" were chosen, would the number
fall back to T-000001 rather then go to T-000034 if the first 33 records
were "FIN"?

My sample does work like this. If you'd like me to send you a copy, send me
a private e-mail message with a valid reply-to address. My e-mail address is
available at the bottom of the contributor's page indicated below. Please do
not post your e-mail address (or mine) to a newsgroup reply. Doing so will
only attract the unwanted attention of spammers.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________