Error from Delete query with inner join

[Rick Beach]

Below is the query that I am attempting to run:

DELETE DISTINCTROW Equipment.*
FROM CHSRRequestInProgressB INNER JOIN Equipment ON CHSRRequestInProgressB.JTag = Equipment.[EquipJ-tag#];

I receive an error stating "Could not delete from specified tables". The table I need the rows deleted from are in the Equipment table. The "[EquipJ-tag#]" is the primary key in this table. I can edit from within the equipment table itself, so the table is not read only. I can run the query in datasheet view and the information is correct, but I can not update the information.

Please assist.

Rick

[Rabbit]

Why is DISTINCTROW in there?

[Rick Beach]

Per the above, If the table CHSRRequestInProgressB is removed, the ability to change or delete records is possible. When this query is added as an Inner Join, the query becomes read only. The JTAG in the CHSRRequestInProgressB query is a combination of a text field + a data field from table + a data field from table. Below is the exact information for creating the jtag:

JTag: "CHSR" & [EquipmentRequested].[CHSR] & "-" & [EquipmentRequested].[InitiatedDate]

I do not understand why the CHSRRequestInProgressB would have any impact when I only want to delete from the Equipment table.

I hope this helps in getting the answer.

[Rick Beach]

I have attempted with the DISTINCTROW and without it. It gives the same error message.

[Rabbit]

That doesn't answer my question about why DISTINCTROW is in there. By necessity, DISTINCTROW makes a query non-editable.

[Rick Beach]

As stated, this has also been removed and I still recieve the same error. It was put in place because searching for answers, this needed to be in place if the table being deleted from was not the primary key. In my table the primary key is a combination of the JTAG, StatusID and EquipLoc.

[dsatino]

Try this:

DELETE e.*
FROM Equipment e
WHERE e.[EquipJ-tag#] IN(SELECT chs.JTag FROM CHSRRequestInProgressB chs)
 

**Before you run this, note that I'm assuming you want to delete any record from the equipment table that appears in chsrequest table.

[Rick Beach]

I am attempting to run the query you stated above. It has been running for 10 minutes and has not completed. You are correct in you assumption listed above.

Thanks

[dsatino]

Usually delete queries only take seconds, or less. My guess is that there are a lot of records in the chs table.
Try this:

DELETE e.*
FROM Equipment e
WHERE e.[EquipJ-tag#] IN(SELECT chs.JTag FROM CHSRRequestInProgressB chs GROUP BY chs.JTag)

[Rick Beach]

This gives an error: You tried to execute a querythat does not include the specified expression 'e.[EquipJ-tag#]=' as part of an aggregate function.

[dsatino]

Ok, I guess it doesn't like the alias'. The following removes those. Also, is your table in Access or on a different type of server?


DELETE Equipment.*
FROM Equipment
WHERE Equipment.[EquipJ-tag#] IN(SELECT CHSRRequestInProgressB.JTag FROM CHSRRequestInProgressB GROUP BY CHSRRequestInProgressB.JTag)

[Rick Beach]

This gives the same aggregate function error. I believe it is the "Group By" statement.
The table is an access table.

[Rick Beach]

I have also attempted the below statement:

DELETE Equipment.*
FROM Equipment
WHERE (((Equipment.[EquipJ-tag#])=(SELECT CHSRRequestInProgressB.JTag
FROM CHSRRequestInProgressB)));

This gives an error stating "At most one record can be returned by this subquery." The help section states "A subquery of this kind cannot return more than one record. Revise the SELECT statement of the subquery to request only one record."

[dsatino]

There shouldn't be an '=' in there.

Switch you query to SQL view and post back what it has int there.

[Rabbit]

There must be something else going on, can you attach your database?

[Rick Beach]

I cannot seem to attach a txt file or a zip file. I receive an error stating a security token was missing.

[Rabbit]

Try logging out and then logging back in.

[Rick Beach]

I have logged off and back on. I even closed the browser and reopened. I still receive the same error stating "Your submission could not be processed because a security token was missing".

[Rabbit]

Alright, we'll have to make do with descriptions.

Can you give us your relevant table definitions, i.e. column names and column types. And your relevant query definitions.

[Rick Beach]

Below are the two tables used for the queries:

Equipment
EquipJ-tag# Text
StatusID Number
CabJ-tag# Text
EquipLoc Text
Manufacturer Text
Model Text
SerNum Text
NumCords Number
Floor Text
Notes Text
Updated Date/Time
PlanID Number
HLAmps Number

EquipmentRequested
RequestedBy Text
WorkPhone Text
MocRoc Text
HPCostCtr Text
WorkType Text
Building Text
Location Number
CHSRLOC Number
PanelID Text
SINumber Text
RIDNumber Text
CHSRNumber Number
CHSRLetter Text
Model Number
Manufacturer Number
Customer Text
AssocSystem Text
ProjectName Text
InitiatedDate Date/Time
RequestedStartDate Date/Time
RequestedCompleteDate Date/Time
ActualCompleteDate Date/Time
ConnectorType Text
NumberOfCords Number
BreakerAmps Number
NumberOfPoles Number
Voltage Number
Notes Text
Notes2 Text
Pre-InstallNotes Text
Status Text
CompletedBy Text
OrigCHSRNumber Number
Overtime Yes/No
AuditDate Date/Time
AuditComplete Yes/No
InstallRDCLoc Text
Phase Text
UPS Text
BreakerType Text
HLAmps Number

Below is the query to select the cancelled EquipmentRequested records:

SELECT "CHSR" & [EquipmentRequested].[CHSRNumber] & "-" & [EquipmentRequested].[InitiatedDate] AS JTag
FROM EquipmentRequested
WHERE (((EquipmentRequested.Status)="CANC"));

Below is the query to delete the records from the EquipmentTable:

DELETE Equipment.*
FROM CHSRRequestInProgressB INNER JOIN Equipment ON CHSRRequestInProgressB.JTag = Equipment.[EquipJ-tag#];

Below is a sample of an EquipmentRequested record in a comma delimited format:

RequestedBy,WorkPhone,MocRoc,HPCostCtr,WorkType,Bu ilding,Location,CHSRLOC,PanelID,SINumber,RIDNumber ,CHSRNumber,CHSRLetter,Model,Manufacturer,Customer ,AssocSystem,ProjectName,InitiatedDate,RequestedSt artDate,RequestedCompleteDate,ActualCompleteDate,C onnectorType,NumberOfCords,BreakerAmps,NumberOfPol es,Voltage,Notes,Notes2,Pre-InstallNotes,Status,CompletedBy,OrigCHSRNumber,Ove rtime,AuditDate,AuditComplete,InstallRDCLoc,Phase, UPS,BreakerType,HLAmps
Glenn Goodale,939-5662,,US7045895,Electric Install,C2,19542,19542,4233,,,45949,Z,1104,128,,SA BRE,,1/3/2011,12/30/2010,1/14/2011,1/5/2011,H2720,,30,3,208,30A/250V 3 PHASE UPS 6,,,CANC,Unk,,FALSE,,FALSE,C2.M.46,ABC,6,GE,6

Below is a sample of an Equipment record in a comma delimited format:

EquipJ-tag#,StatusID,CabJ-tag#,EquipLoc,Manufacturer,Model,SerNum,NumCords,F loor,Notes,Updated,PlanID,HLAmps,s_GUID,s_ColLinea ge,s_Generation,s_Lineage
CHSR45948-1/4/2011,9,,C2.J41.2,HP/COMPAQ,C7000,,0,C2,,1/4/2011,,6,,,17791,

Please let me know if I can supply any other information that would help.

[Rick Beach]

On the above sample of an Equipment Record, change the CHSR45948-1/4/2011 to CHSR45949-1/5/2011 so both records will match. I accidentally send one record off from the Equipment table.

[Rick Beach]

I made an error in the date for the above Equipment record, it should read CHSR45949-1/3/2011. I think I need to pay closer attention.

Thanks

[Rabbit]

Your EquipJ-tag# is CHSR45949-1/3/2011 but when you rebuild your JTag using "CHSR" & [EquipmentRequested].[CHSRNumber] & "-" & [EquipmentRequested].[InitiatedDate], you're going to get CHSR45949-01/03/2011 because the date is returned in the format 01/03/2011 and not 1/3/2011 and so they don't match.

[Rick Beach]

I have the query result on the screen and it is:

CHSR45949-1/3/2011

This was copied from the query. There are no leading zero's in the date format.

[Rick Beach]

Also note that if the delete query is run in Datasheet view, the record is found and listed.

[Rabbit]

What version of Access are you using? Mine shows leading zeroes.

[Rick Beach]

I forced the date to be 01032011 in the query and changed the table to reflect CHSR45949-01032011 and I still am not able to delete the record. The record is still not updateable. I will change this back to the original format of CHSR45949-1/3/2011.

[Rick Beach]

I have Microsoft Access 2010.

[Rabbit]

Are you sure this doesn't work?

DELETE *
FROM Equipment
WHERE (((Equipment.[EquipJ-tag#]) In (SELECT JTag FROM CHSRRequestInProgressB)));
 

Because it works when I try it.

[Rick Beach]

The query listed above is different than any others that were sent. This one works. I really appreciate all of your time and assistance.

[Rabbit]

In essence it's the same as dsantino's suggestion but it is syntactically different.

[Rick Beach]

In Dsantino's the parenthesis were the onle real differences that I notice once the tags "e" and "chs" are removed:

Dsantino's where statement:
WHERE Equipment.[EquipJ-tag#] IN(SELECT JTag FROM CHSRRequestInProgressB)

Your where statement:
WHERE (((Equipment.[EquipJ-tag#]) In (SELECT JTag FROM CHSRRequestInProgressB)));

I do appreciate both of your assistance.

I don't know why something so simple has to become so complicated.

[Rabbit]

Not a problem, good luck.

In the end, the parentheses really don't make a difference. It was probably the e and chs that was screwing it up. The syntax he was using is used in SQL Server and possibly Oracle. However, Access doesn't understand what that means. It would have to be AS e and AS chs for that to work in Access.

[Rick Beach]

Actually, his programming works. That was when I stated the query was running after 20 minutes. Once I used the full tables, yours also does the same. There are 29,051 records in the Equipment table and 47,318 records in the Equipment Requested table. Do you have any suggestions to make this run faster?

[Rabbit]

Put indexes on EquipJ-tag#, CHSRNumber, InitiatedDate, and Status.

[Rick Beach]

You are amazing.....

Thanks