Hi Andy,
Try the following update query:
UPDATE MH_IPEP
INNER JOIN IPEPsCDS1 ON MH_IPEP.IHROLL = CLng(IPEPsCDS1.ProvSpellR6)
SET MH_IPEP.IPDIAG1 = [ICDPRIMARY],
MH_IPEP.IPDIAG2 = [ICDSUBS],
MH_IPEP.IPDIAG3 = [ICD1],
MH_IPEP.IPDIAG4 = [ICD2],
MH_IPEP.IPDIAG5 = [ICD3];
I'm using CLng instead of CInt, just in case the value of a text based key
exceeds 32,767 (ie., the upper limit of an integer).
Both tables really have an underlying design problem. It appears as if the
MH_IPEP table has five fields that contain similar data: IP Diagnostic codes
of some type. The IPEPsCDS1 table has at least three fields that seem to
represent similar data: ICDx codes. This type of design is storing a
one-to-many relationship within single tables. What happens if you need to
add a sixth IPDIAG field, or a fourth ICD field to one of the tables? You'll
end up having to modify any queries, forms & reports in order to accomodate
this new need. Storing similar data in repeating fields is not good database
design. This should raise an immediate red flag. Here is some advice to
remember:
Records are cheap; Fields are expensive
In other words, your design should be such that you can add or remove a
IPDIAG or ICDx value simply by adding or removing records from tables. If
your design requires the addition or deletion of fields, the cost can become
expensive.
Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________
:
Can you help out a little with the SQL script? Currently the update query
(sorry thought it was a make table query) reads:
UPDATE MH_IPEP INNER JOIN IPEPsCDS1 ON MH_IPEP.IHROLL =
IPEPsCDS1.ProvSpellR6 SET MH_IPEP.IPDIAG1 = [ICDPRIMARY], MH_IPEP.IPDIAG2 =
[ICDSUBS], MH_IPEP.IPDIAG3 = [ICD1], MH_IPEP.IPDIAG4 = [ICD2],
MH_IPEP.IPDIAG5 = [ICD3];
The join between the tables MH_IPEP and IPEPsCDS1 with fields IHROLL
(numeric) and ProvSpellR6 (text) respectively, is causing the problem. Can
you help with either giving SQL specific for the update query, or
alternatively IPEPsCDS1 is created from a make table query before this update
query is run and could we update the data type of ProvSpellR6 to numeric
here??? The SQL for the make table query is below as well:
SELECT tblMonthlyImport1.HospProvSpellNo, Left([HospProvSpellNo],6) AS
ProvSpellR6, tblMonthlyImport1.PatID, tblMonthlyImport1.AdmDate,
tblMonthlyImport1.DisDate, tblMonthlyImport1.EpStartDate,
tblMonthlyImport1.EpEndDate, tblMonthlyImport1.ConsSpecCode,
tblMonthlyImport1.ICDPrimary, tblMonthlyImport1.ICDSubs,
tblMonthlyImport1.ICD1, tblMonthlyImport1.ICD2, tblMonthlyImport1.ICD3,
tblMonthlyImport1.DisMethod, [New_Numeric] AS Expr1 INTO IPEPsCDS1
FROM tblMonthlyImport1
WHERE (((tblMonthlyImport1.ConsSpecCode)="710" Or
(tblMonthlyImport1.ConsSpecCode)="715"));
Thanks
__________________________________________
:
Hi Andy,
Yes, it's making more sense, although I not sure why you have a need to join
a numeric field to a text field. The change you were after is not considered
a format change. It would be a data type change to the field. However, before
going down that road, I think (untested) that there is an easier solution.
In your query, use either the CInt or CStr function to convert to integer or
string, respectively. For example, the following Make Table query can be made
to work in the Northwind Sample database, after adding a new text field to
the Employees table. The new text field is named "NewTextField", and has
values from 1 to 8 added to each record:
SELECT Categories.CategoryName, Employees.LastName
INTO NewTableNameHere
FROM Categories
INNER JOIN Employees
ON Categories.CategoryID = CInt(Employees.NewTextField);
Notice the use of CInt on the last line of the SQL statement. To view the
SQL statement for your query, click on View > SQL View in query design view.
No design changes required at the table level! Isn't that slick?
Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________
:
Table 1 has a field (A) which is numeric format. Table 2 (B) has a field
which is text format. Within a make table query for Table 3, table 1 & 2 are
joined via fields A & B. The make table query will not run due to the data
mismatch error because the two related joins between the tables are not of
the same format. To get the query to run, I have to go into design view of
table 1, alter the format of field A to text, save changes and then run the
query.
I would like to be able to build a function into a macro, so that I can
eliminate this manual change of field and automate it.
Hope this makes a little more sense.
__________________________________________
:
Hi Andy,
What exactly are you needing to alter? Format changes only affect the way in
which data is displayed; they do not change the underlying data. It sounds
like you want to toggle some setting back and forth (ie. " I currently have
to manually go into the design view of the table and alter the format ").
Perhaps if you explain a bit more, we can help.
Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________
:
Hi there, I need to alter the format of a table field with some script or a
query to build into a macro. I currently have to manually go into the design
view of the table and alter the format so that a new query can be run between
2 tables.
Would then need to build this format change section into a macro, HELP!
Cheers,
A
