Append query Fail. Source Column changes its own values.

[carojasa]

HI ALL.
BASICALLY I HAVE TWO TABLES.
Table1 = t5rankingss
Table2 = 5_ProductionModule

From left to right the fields in Table1 are:
ID
CPA Pretty Well ID
mmmdate
QryRank

From left to right the fields in Table2 are:
CPA Pretty Well ID
mDate


PRD Monthly GAS e3m3
PRD Monthly OIL m3
PRD Monthly WTR m3
PRD Monthly CND m3
PRDMonthly HRS hrs
RankSS

What I want?
To have the field Table1 (QryRank) imported into Table2 as a new column called RankSS

My primary ID is CPA Pretty Well ID.
Should I remove ALL relationship prior to appending this information?

An attempt to append the field resulted in the column QryRank not honoring its original values.
Below is what the system is now giving me... See how the values in the last field changed to 1's

[June7]

How are you generating the QryRank values?

[carojasa]

How are you generating the QryRank values?

The QryRank values came from:
SELECT *
FROM (SELECT a1.ID, a1.mdate, a1.[PRD Monthly HRS hrs], COUNT(*) AS QryRank FROM QueryTest AS a1
INNER JOIN
QueryTest AS a2 ON
(a1.mdate >= a2.mdate) AND
(a1.id = a2.id)
GROUP BY
a1.ID
, a1.mdate
, a1.[PRD Monthly HRS hrs]) AS RankingQuery;


But the QryRank values are now part of a Table... i dont think they are sourced from the query... I made sure to save the qry results as a table.

[orange]

I mocked up your tables.

ID	CPA Pretty WELL ID	mmmdate	qryRank	jid
1171	100/01-01-034-04w5/00	01/06/2014	1	1
1171	100/01-01-034-04w5/00	01/07/2014	2	2
1171	100/01-01-034-04w5/00	01/08/2014	3	3
1171	100/01-01-034-04w5/00	01/09/2014	4	4
1171	100/01-01-034-04w5/00	01/10/2014	5	5
3045	100/01-01-041-06w5/00	01/10/2012	1	6
3045	100/01-01-041-06w5/00	01/11/2012	2	7

Note the PRD values below are totally fictitious since they aren't part of the Ranking issue.

CPA Pretty Well ID	mDate	PRD Monthly GAS e3m3	PRD Monthly OIL m3	PRD Monthly WTR m3	PRD Monthly CND m3	PRDMonthly HRS hrs	RankSS	jjid
100/01-01-034-04w5/00	01-06-2014	11	22	33	44	55		1
100/01-01-034-04w5/00	01-07-2014	12	23	34	45	56		2
100/01-01-034-04w5/00	01-08-2014	13	24	35	46	57		3
100/01-01-034-04w5/00	01-09-2014	14	25	36	47	58		4
100/01-01-034-04w5/00	01-10-2014	15	26	37	48	59		5
100/01-01-041-06w5/00	01-10-2012	16	27	38	49	60		6
100/01-01-041-06w5/00	01-11-2012	17	28	39	50	61		7

Your table t5Rankings is Ranked based on your ID and mmmDate, but since ID is used for the same grouping as
CPA Pretty WELL ID, I created a unique composite index based on
-CPA Pretty WELL ID and mmmDate and used the related fields in 5_ProductionModule to make another unique composite index --namely CPA Pretty WELL ID and mDate. (see attached graphic)

Next was to create an update query that joined the tables on these indexed fields, update the 5_ProductionModule.RankingSS with the related values from t5Rankings.QryRank

The query SQL:

UPDATE 5_ProductionModule INNER JOIN t5Rankingss 
ON ([5_ProductionModule].mDate = t5Rankingss.mmmdate) AND 
([5_ProductionModule].[CPA Pretty Well ID] = t5Rankingss.[CPA Pretty WELL ID]) 
SET [5_ProductionModule].RankSS = [t5Rankingss]![qryRank];

The final result is

CPA Pretty Well ID	mDate	PRD Monthly GAS e3m3	PRD Monthly OIL m3	PRD Monthly WTR m3	PRD Monthly CND m3	PRDMonthly HRS hrs	RankSS	jjid
100/01-01-034-04w5/00	01-06-2014	11	22	33	44	55	1	1
100/01-01-034-04w5/00	01-07-2014	12	23	34	45	56	2	2
100/01-01-034-04w5/00	01-08-2014	13	24	35	46	57	3	3
100/01-01-034-04w5/00	01-09-2014	14	25	36	47	58	4	4
100/01-01-034-04w5/00	01-10-2014	15	26	37	48	59	5	5
100/01-01-041-06w5/00	01-10-2012	16	27	38	49	60	1	6
100/01-01-041-06w5/00	01-11-2012	17	28	39	50	61	2	7

Note: The extra fields jjId and Jid were added by me and made PK of the table. They don't play into the actions above.

I hope this is useful.

[carojasa]

Hi Orange.
I tried the update query as outlined above.
The result was the production of a table with only one column/field (RankSS). This table is complety empty.
Below are the images of what you suggested with the indexes...
The sql code used and the end result.





The end Result is


The code is (basically copy and paste):
UPDATE 5_ProductionModule INNER JOIN t5Rankingss
ON ([5_ProductionModule].mDate = t5Rankingss.mmmdate) AND
([5_ProductionModule].[CPA Pretty Well ID] = t5Rankingss.[CPA Pretty WELL ID])
SET [5_ProductionModule].RankSS = [t5Rankingss]![qryRank];

Any ideas?

[orange]

Hmmm. We are missing a few things in communication.
I made table designs based on your post. I added fields Jid in 1 table and JJId in the other. I made these autonumbers and Primary Keys. (more of a backup in case there was a problem)

I made unique composite indexes in each table field [CPA Pretty WELL ID] and M(mm)Date. These unique indexes are in addition to the PK field I added.

Your jpg shows the PrimaryKey as [CPA Pretty WELL ID] and M(mm)Date???

ID is not autonumber on my tables.
You have backups RIGHT?

attached is design of the T5 table

[carojasa]

I tried to correct things and I am still getting the same result.

Table 1 is shown below. An image of the design view and the Indexes is shown too.



Table two is also shown below along with is design view and indexes window. I am attaching these so that you can see how I have assembled the indexes.



I also included the Jid and JJid Autonumber columns. I don't know whether these values should match from table to table...... this seems to not be the case.

The update query gives me a table with only 1 column (RankSS) and completely empty.

Thanks for you help

[orange]

Do you have a database now with both of these revised tables T5... and 5_...?
Can you post it?

In overview, in database tables have fields that uniquely identify records in that table. You can join tables on fields with same datatypes. Your fields that uniquely identify records in your table are CPA.. and mDate or mmmdate depending on the table.

The update query is matching records in these tables based on the values in CPA... and MDate and CPA and mmmDate in the other table. Where the values match, the update query is saying, change the value in Rankss with the value of QryRank in the other table.

[carojasa]

Do you have a database now with both of these revised tables T5... and 5_...?
Can you post it?

In overview, in database tables have fields that uniquely identify records in that table. You can join tables on fields with same datatypes. Your fields that uniquely identify records in your table are CPA.. and mDate or mmmdate depending on the table.

The update query is matching records in these tables based on the values in CPA... and MDate and CPA and mmmDate in the other table. Where the values match, the update query is saying, change the value in Rankss with the value of QryRank in the other table.

I understand your logic now. Thank you for the explanation. How can I post my database if it is 500 mbs?
The update query makes sense to me.... but it yields no results when i run it.

[orange]

carajosa,

You may see something in the attached.

[June7]

2MB zip file attachment allowed. If your zip exceeds that then can upload to fileshare site such as Box.com and post link.

[orange]

carojasa, (sorry for the previous misspellings)

Here is the jpg showing the Update query and the message

See PM for database disposition