joining tables

[mls via AccessMonster.com]

I have to join 2 tables.
I created a table with all the field names as place holder called "DUMMY
TABLE", and I have another table with real values called "ABC table". Now I
need to merge both the tables and should have only one column for each field.
say for example date is present in both tables. I have to keep date from
abc_val and ignore date from dummy table. i.e how can I overide the dummy
fields
I don't have any joining conditions for these tables

SELECT temp.*, abc_val.* from dummy temp, abc_xl abc_val;

 

[KARL DEWEY]

I don't have any joining conditions for these tables
How do you expect to know which record goes with which?

 

[John W. Vinson]

I have to join 2 tables.
I created a table with all the field names as place holder called "DUMMY
TABLE", and I have another table with real values called "ABC table". Now I
need to merge both the tables and should have only one column for each field.
say for example date is present in both tables. I have to keep date from
abc_val and ignore date from dummy table. i.e how can I overide the dummy
fields
I don't have any joining conditions for these tables

SELECT temp.*, abc_val.* from dummy temp, abc_xl abc_val;

Let me rephrase the question in non-database terms:

I have a stack of envelopes addressed to people. I also have a stack of checks
addressed to people.

Without considering the names on the checks or on the envelopes, how can I
send each person their check?

If you don't have any way to join the tables there is no way - even in
principle! - to do what you ask.

What are the fieldnames in these tables? What real-life person, thing or event
does each table represent? How can you tell which date from DUMMY goes with
which date field from ABC?

 

[mls via AccessMonster.com]

I am trying to automate a process where different tests will be performed
each time.
suppose if test_a, test_b is perofrmed on jan 20 and test_b, test_c, test_d
performed on Jan21. I need to load these information into the database. Every
day say 5 samples were tested for 5 different test, which may not be the same
tests so I am having a place holder for all these test in a dummy table and
combine the 2 tables. Then insert statement is used to load into the database.
All my 67 fields will get updated. Say if a test is not performed then it
will have value as null not 0 as numeric field and if a test has result then
that value is store into the field.
This might sound crasy but I have an fully automated process running
efficiently in another programming language and I am trying to convert all
that to ACCESS.
In my current process I read dummy table first so all the fields will be
loaded then I read actual table so they will override the dummy fields with
the real value for the test performed.( It doesn't create tablename.fieldname
) , where as if I use SQL it is creating 2 different columns with table
prefix..

SELECT * into ALL_TESTS
FROM DUMMY, TEMP_RESULTS;

I have to join 2 tables.
I created a table with all the field names as place holder called "DUMMY

[quoted text clipped - 6 lines]

SELECT temp.*, abc_val.* from dummy temp, abc_xl abc_val;

Let me rephrase the question in non-database terms:

I have a stack of envelopes addressed to people. I also have a stack of checks
addressed to people.

Without considering the names on the checks or on the envelopes, how can I
send each person their check?

If you don't have any way to join the tables there is no way - even in
principle! - to do what you ask.

What are the fieldnames in these tables? What real-life person, thing or event
does each table represent? How can you tell which date from DUMMY goes with
which date field from ABC?

 

[John W. Vinson]

I am trying to automate a process where different tests will be performed
each time.
suppose if test_a, test_b is perofrmed on jan 20 and test_b, test_c, test_d
performed on Jan21. I need to load these information into the database. Every
day say 5 samples were tested for 5 different test, which may not be the same
tests so I am having a place holder for all these test in a dummy table and
combine the 2 tables. Then insert statement is used to load into the database.
All my 67 fields will get updated. Say if a test is not performed then it
will have value as null not 0 as numeric field and if a test has result then
that value is store into the field.
This might sound crasy but I have an fully automated process running
efficiently in another programming language and I am trying to convert all
that to ACCESS.
In my current process I read dummy table first so all the fields will be
loaded then I read actual table so they will override the dummy fields with
the real value for the test performed.( It doesn't create tablename.fieldname
) , where as if I use SQL it is creating 2 different columns with table
prefix..

Let me ask again. I'll be happy to help if you will answer my question. I'm
unable to do so if you do not:

What are the [some representative] fieldnames in these tables? What real-life
person, thing or event does each table represent? How can you tell which date
from DUMMY goes with which date field from ABC?

In a properly normalized database you do NOT need a "dummy table".

 

[mls via AccessMonster.com]

I have a form that takes machine generated .xls file which has test results
of an sample. Each .xls file has different Test result so I need to run my
program to take the _Ct values and automatically process
_Tested and _Results with an array as below and insert these values into the
database. These test vary every time so I want to code as generic as
possible so that in future if new tests are added to the machine then it will
be easy to handle by just changing CONST values


Public Const ct= “InfA_Ct InfB_Ct H1_Ct H3_Ct H7na_Ct H7eu_Ct H9_Ctâ€
Public Const test= “InfA_Tested InfB_Tested H1_Tested H3_Tested†_
“H7na_Tested H7eu_Tested H9_Testedâ€
Public Const result= “InfA_Result InfB_Result H1_Result H3_Result†_
“H7na_Result H7eu_Result H9_Resultâ€


Dim array_ct() As String
Dim array_t() As String
Dim array_r() As String
Dim i As Integer

array_ct = Split(ct, " ")
array_t = Split(Test, " ")
array_r = Split(result, " ")

For i = 1 To UBound(array_ct)
If array_ct(i) > 1 And array_ct(i) < 30 Then Do
array_t(i) = "1"
array_r(i) = "1"
End

Else: If array_ct(i) = 0 Or array_ct(i) > 30 Then Do
array_t(i) = "1"
array_r(i) = "0"
End

End

I am trying to automate a process where different tests will be performed
each time.

[quoted text clipped - 14 lines]

) , where as if I use SQL it is creating 2 different columns with table
prefix..

Let me ask again. I'll be happy to help if you will answer my question. I'm
unable to do so if you do not:

What are the [some representative] fieldnames in these tables? What real-life
person, thing or event does each table represent? How can you tell which date
from DUMMY goes with which date field from ABC?

In a properly normalized database you do NOT need a "dummy table".

 

[John W. Vinson]

I have a form that takes machine generated .xls file which has test results
of an sample. Each .xls file has different Test result so I need to run my
program to take the _Ct values and automatically process
_Tested and _Results with an array as below and insert these values into the
database. These test vary every time so I want to code as generic as
possible so that in future if new tests are added to the machine then it will
be easy to handle by just changing CONST values

I will ask for the third time; and emphasize again that I CANNOT HELP YOU
WITHOUT THIS INFORMATION.

What is the structure of your table?

What are the fields?

You have been talking about your proposed solution. Without knowing the
structure of your table I cannot tell whether the proposed solution needs
tweaking, complete rewrite, or whether your table structure is at fault and
needs to be redone.

I'm sorry to be so starchy about this, but it's really frustrating to ask a
simple question three times and get *NO ANSWER* to it. So:

What is the structure of the table into which you are inserting this data?
What are its fieldnames?
What is its Primary Key?

 

[mls via AccessMonster.com]

My master Table field names: SampleID testno InfA_Ct InfB_Ct H1_Ct H3_Ct
H7na_Ct H7eu_Ct H9_Ct InfA_Tested InfB_Tested H1_Tested H3_Tested
H7na_Tested H7eu_Tested H9_Tested InfA_Result InfB_Result H1_Result H3_Result
H7na_Result H7eu_Result H9_Result dateTested filename FinalResult

My transactional table field names: SampleID testno dateTested filename
FinalResult
+ any combination of the following ( InfA_Ct InfB_Ct H1_Ct) or (H7na_Ct
H7eu_Ct) or (_Ct InfB_Ct H1_Ct H3_Ct H7na_Ct) all the possible combinations..

Primary: It’s a composite key with SAMPLEID + TestNo

I have a form that takes machine generated .xls file which has test results
of an sample. Each .xls file has different Test result so I need to run my

[quoted text clipped - 3 lines]

possible so that in future if new tests are added to the machine then it will
be easy to handle by just changing CONST values

I will ask for the third time; and emphasize again that I CANNOT HELP YOU
WITHOUT THIS INFORMATION.

What is the structure of your table?

What are the fields?

You have been talking about your proposed solution. Without knowing the
structure of your table I cannot tell whether the proposed solution needs
tweaking, complete rewrite, or whether your table structure is at fault and
needs to be redone.

I'm sorry to be so starchy about this, but it's really frustrating to ask a
simple question three times and get *NO ANSWER* to it. So:

What is the structure of the table into which you are inserting this data?
What are its fieldnames?
What is its Primary Key?

 

[John W. Vinson]

My master Table field names: SampleID testno InfA_Ct InfB_Ct H1_Ct H3_Ct
H7na_Ct H7eu_Ct H9_Ct InfA_Tested InfB_Tested H1_Tested H3_Tested
H7na_Tested H7eu_Tested H9_Tested InfA_Result InfB_Result H1_Result H3_Result
H7na_Result H7eu_Result H9_Result dateTested filename FinalResult

This table is in fact not properly normalized. You're storing data in
fieldnames. A "tall-thin" table with fields for ResultType and Result would
serve you far better!

My transactional table field names: SampleID testno dateTested filename
FinalResult
+ any combination of the following ( InfA_Ct InfB_Ct H1_Ct) or (H7na_Ct
H7eu_Ct) or (_Ct InfB_Ct H1_Ct H3_Ct H7na_Ct) all the possible combinations..

Primary: It’s a composite key with SAMPLEID + TestNo

You'll need to open a Recordset of your table, and use some rather snarky code
to translate the text value in your input into the fieldname of the result.
I've got to run do some shopping this afternoon, but I'll try to take a look
at it. I would really, really recommend that you normalize your table
structure though, it'll make the code MUCH easier.

 

[mls via AccessMonster.com]

Lab team is not that technical to think about normalization all they want is
to have the data in one back-end table and one front end FORM.

how can I translate the fields?

 

[John W. Vinson]

Lab team is not that technical to think about normalization all they want is
to have the data in one back-end table and one front end FORM.

And you're saying that you want them to have all the data in a *badly
designed, denormalized, highly code dependent, hard to upgrade, hard to use*
table, just so they can look at the table datasheet?

Sorry, but that sounds irresponsible to me. Don't confuse data PRESENTATION
with data STORAGE! They are different tasks with different requirements; you
can have a properly normalized table with the form set up in a user-friendly
manner. It's possible (difficult but possible) to do what you ask, but you're
really painting yourself into a corner.

how can I translate the fields?

Just to clarify: do you want to *insert new records* into the master table
from the transactional table (if so an Append Query would do)? Or do you want
to *update existing records*, say for a given SampleID?

Does either table have a Primary Key, or a candidate primary key? For example,
would there be only one record for a given SampleID - or for a given
combination of SampleID and Testno - or for a given combination of SampleID,
Testno and DateTested? Will the cryptic fieldnames such as H1_Ct and H7na_Ct
be consistant over time, or will you be adding new values?

 

[mls via AccessMonster.com]

I want to Append. Yes, there will be one record for a given combination of
SampleID and Testno. Date Tested can be same some times because same samples
will be tested twice on same day if the results are not clear. H1_Ct etc will
change for each test, new values are generated for the same sample if it is
tested twice.

Lab team is not that technical to think about normalization all they want is
to have the data in one back-end table and one front end FORM.

And you're saying that you want them to have all the data in a *badly
designed, denormalized, highly code dependent, hard to upgrade, hard to use*
table, just so they can look at the table datasheet?

Sorry, but that sounds irresponsible to me. Don't confuse data PRESENTATION
with data STORAGE! They are different tasks with different requirements; you
can have a properly normalized table with the form set up in a user-friendly
manner. It's possible (difficult but possible) to do what you ask, but you're
really painting yourself into a corner.

how can I translate the fields?

[quoted text clipped - 11 lines]
Just to clarify: do you want to *insert new records* into the master table
from the transactional table (if so an Append Query would do)? Or do you want
to *update existing records*, say for a given SampleID?

Does either table have a Primary Key, or a candidate primary key? For example,
would there be only one record for a given SampleID - or for a given
combination of SampleID and Testno - or for a given combination of SampleID,
Testno and DateTested? Will the cryptic fieldnames such as H1_Ct and H7na_Ct
be consistant over time, or will you be adding new values?