Deleting records problem

F

Frank

sDeleting records?
I have a multiple tabled database and wanted to delete all the records of 4
of the tables. A delete query allows me to do this. After the deletion I then
want to input new data in the record field of the table from which I deleted
the records but this causes a problem. All the query and reports become non
available because the query’s locks and become unreadable. Could someone
please give a possible reason for this?

Thank you
 
J

John W. Vinson

sDeleting records?
I have a multiple tabled database and wanted to delete all the records of 4
of the tables. A delete query allows me to do this. After the deletion I then
want to input new data in the record field of the table from which I deleted
the records but this causes a problem. All the query and reports become non
available because the query’s locks and become unreadable. Could someone
please give a possible reason for this?

Thank you
Click to expand...

You'll have to explain the problem more clearly. The "query's locks"? Queries
don't have locks. Or do you mean that the query locks up when you try to run
it? Please post some more information about the structure and relationships of
your tables, perhaps the SQL view of the query, and the specific symptoms
(error messages for example).
 
J

John W. Vinson

sDeleting records?
I have a multiple tabled database and wanted to delete all the records of 4
of the tables. A delete query allows me to do this. After the deletion I then
want to input new data in the record field of the table from which I deleted
the records but this causes a problem. All the query and reports become non
available because the query’s locks and become unreadable. Could someone
please give a possible reason for this?

Thank you
Click to expand...

You'll have to explain the problem more clearly. The "query's locks"? Queries
don't have locks. Or do you mean that the query locks up when you try to run
it? Please post some more information about the structure and relationships of
your tables, perhaps the SQL view of the query, and the specific symptoms
(error messages for example).
 
F

Frank

:

Or do you mean that the query locks up when you try to run

There is a total of 20 query's that is used for reports that worked fine
with the example data I have used to design the database.. As soon as I
delete the records of 4 of the tables, children's names. their acvievements
and events and event records, they took part in, to start a new tracmeeting,
the query's running the reports locks up in such away that there are no data
on any of the reports. Not even #error. If I delete every record but one in
each of the tables I still can use the database with no problem and all the
reportst is fine.
Sorry for this long explanation but it is impossiblel to sen all te SQLl
statements or query's I just want a thought or two on why this might happen.

Thanks for your time and effort
 
F

Frank

:

Or do you mean that the query locks up when you try to run

There is a total of 20 query's that is used for reports that worked fine
with the example data I have used to design the database.. As soon as I
delete the records of 4 of the tables, children's names. their acvievements
and events and event records, they took part in, to start a new tracmeeting,
the query's running the reports locks up in such away that there are no data
on any of the reports. Not even #error. If I delete every record but one in
each of the tables I still can use the database with no problem and all the
reportst is fine.
Sorry for this long explanation but it is impossiblel to sen all te SQLl
statements or query's I just want a thought or two on why this might happen.

Thanks for your time and effort
 
J

John Spencer MVP

It sounds as if you are saying the report is blank if you delete all the
records in the table that the report is using.

No records usually equates to no report or at least no data to report.

I apologize if I have misunderstood.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
J

John Spencer MVP

It sounds as if you are saying the report is blank if you delete all the
records in the table that the report is using.

No records usually equates to no report or at least no data to report.

I apologize if I have misunderstood.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
F

Frank

:

I apologize if I have misunderstood.
Click to expand...

No I must apologize for my own lack of clearly explain, but a little excuse
is that it is difficult in english as this is my second language. You have
already helped met quit a lot with this database What I meant is that after i
deleted the records and put in new records [delete 100 records put back 10]
the query will not show the data [as it did before] and the data does now
show in the reports.
I think the table and relationships are wrong therefore te error. If I for
instance delete the names table and put in new names [people] the dbase work.
If I delete the names and teamdata then it "locks" no new dat will show - I
can fill in the field of all the tables but the querys wont, show anything

Thanks for your effort
Kind regards
 
F

Frank

:

I apologize if I have misunderstood.
Click to expand...

No I must apologize for my own lack of clearly explain, but a little excuse
is that it is difficult in english as this is my second language. You have
already helped met quit a lot with this database What I meant is that after i
deleted the records and put in new records [delete 100 records put back 10]
the query will not show the data [as it did before] and the data does now
show in the reports.
I think the table and relationships are wrong therefore te error. If I for
instance delete the names table and put in new names [people] the dbase work.
If I delete the names and teamdata then it "locks" no new dat will show - I
can fill in the field of all the tables but the querys wont, show anything

Thanks for your effort
Kind regards
 
J

John W. Vinson

There is a total of 20 query's that is used for reports that worked fine
with the example data I have used to design the database.. As soon as I
delete the records of 4 of the tables, children's names. their acvievements
and events and event records, they took part in, to start a new tracmeeting,
the query's running the reports locks up in such away that there are no data
on any of the reports. Not even #error. If I delete every record but one in
each of the tables I still can use the database with no problem and all the
reportst is fine.
Click to expand...

It sounds like the queries upon which the reports are based include these
tables. If the table is empty then there's no data in it... so there will be
no data in the report.

In addition, if the query Joins the table of children to some other table
using the default join type, you will see no records in the resulting query -
"show all records in TableA and matching records in Children" will return
nothing if there ARE no matching records in Children.

Perhaps you could post the SQL view of just one of the queries that you're
expecting to show data but isn't.
 
J

John W. Vinson

There is a total of 20 query's that is used for reports that worked fine
with the example data I have used to design the database.. As soon as I
delete the records of 4 of the tables, children's names. their acvievements
and events and event records, they took part in, to start a new tracmeeting,
the query's running the reports locks up in such away that there are no data
on any of the reports. Not even #error. If I delete every record but one in
each of the tables I still can use the database with no problem and all the
reportst is fine.
Click to expand...

It sounds like the queries upon which the reports are based include these
tables. If the table is empty then there's no data in it... so there will be
no data in the report.

In addition, if the query Joins the table of children to some other table
using the default join type, you will see no records in the resulting query -
"show all records in TableA and matching records in Children" will return
nothing if there ARE no matching records in Children.

Perhaps you could post the SQL view of just one of the queries that you're
expecting to show data but isn't.
 
F

Frank

John W. Vinson said:
Perhaps you could post the SQL view of just one of the queries that you're
expecting to show data but isn't.
Click to expand...

Query for showing results in a report
ELECT Swemmer.SwemmerID, Item.ItemID, Item.ItNommer, Rekord.RkNo,
Swemmer.SwPunte, Swemmer.SwPlek, Spanne.SpNaam, Spanne.SpAfkort,
Swemmer.SwNaam, Swemmer.SwVan, Swemmer.SwTaal, Swemmer.SwGebore,
Swemmer.SwBepaaloud, DateDiff("yyyy",[SwGebore],[SwBepaaloud]) AS Oud,
Swemmer.SwGalatyd, Swemmer.SwInskryftyd, Swemmer.SwUitdun, Swemmer.SwBaan,
Item.ItAfstand, Item.ItSwemslag, Item.ItGeslag, Item.ItGroepoud,
Rekord.RkNaam, Rekord.RVan, Rekord.RAfstand, Rekord.RSwemslag,
Rekord.RGeslag, Rekord.RGroepoud, Rekord.RTyd, Rekord.RJaartal,
Rekord.RSpnaam, Rekord.RSpAfkort, Spanne.SpInstansie, [SpkonTitel] & " " &
[SpKonNaam] & " " & [SpKonVan] AS Kontak, "Huis telefoon: " &
[SpKonTelefoonhuis] & " Werk telefoon: " & [SpKonTelefoonwerk] & "
Selfoon: " & [SpKonSelfoon] AS Kontaktelefoon, Organiseerder.OInstansie,
[ONaam] & " " & [OTitel] & " " & [Ovan] AS Orgnaam, "Huis telefoon: " &
[OTelefoonhuis] & " Werk telefoon: " & [OTelefoonwerk] & " Selfoon: "
& [OSelfoon] AS Orgtelefone, Byeenkoms.BTipe, Byeenkoms.BJaartal,
Byeenkoms.BDatum, Byeenkoms.Bgalabegin FROM ((Organiseerder INNER JOIN Spanne
ON Organiseerder.OrganiseerderID = Spanne.OrganiseerderID) INNER JOIN
Byeenkoms ON Organiseerder.OrganiseerderID = Byeenkoms.ByeenkomsID) INNER
JOIN ((Item INNER JOIN Rekord ON Item.ItemID = Rekord.OurekordID) INNER JOIN
Swemmer ON Item.ItemID = Swemmer.SwItem) ON Spanne.SpanneID =
Swemmer.SwSpanID;
Query for showing all the reesults for sending to schools
SELECT Item.ItNommer, Swemmer.SwItem, Swemmer.SwGalatyd AS Tyd,
IIf([Swopmerking]="Afw","",IIf([Swopmerking]="Dis","",[Plek])) AS Plekaandui,
Swemmer.SwemmerID, AQ13Werkpunteuit.Punte, AQ13Werkpunteuit.Plek,
IIf([Swopmerking]="Afw",0,IIf([Swopmerking]="Dis",0,IIf(IsNull([Tyd]),0,[Punte])))
AS Marks, Swemmer.Swopmerking, Item.ItAfstand, Item.ItSwemslag,
Item.ItGeslag, Item.ItGroepoud, Spanne.SpanneID, Spanne.SpNaam,
Spanne.SpAfkort, Swemmer.SwSpanID, Swemmer.SwNaam, Swemmer.SwVan,
Swemmer.SwTaal, Swemmer.SwGeslag, Swemmer.SwGebore, Swemmer.SwBepaaloud,
Swemmer.SwInskryftyd, Swemmer.SwGalatyd,
IIf([Swopmerking]="Afw","Afw",IIf([Swopmerking]="Dis","Dis",[Tyd])) AS
Reporttyd, Swemmer.SwUitdun, Swemmer.SwBaan, Swemmer.SwPunte, Swemmer.SwPlek,
Byeenkoms.BTipe, Byeenkoms.BJaartal, Byeenkoms.BDatum, Byeenkoms.BGalabegin,
Organiseerder.OInstansie, Rekord.RkNaam, Rekord.RVan, Rekord.RAfstand,
Rekord.RSwemslag, Rekord.RGeslag, Rekord.RGroepoud, Rekord.RTyd,
Rekord.RJaartal, Rekord.RSpnaam, Rekord.RSpAfkort, [ONaam] & " " & [OTitel] &
" " & [Ovan] AS Orgnaam, "Huis telefoon: " & [OTelefoonhuis] & " Werk
telefoon: " & [OTelefoonwerk] & " Selfoon: " & [OSelfoon] AS
Orgtelefone, [Oinstansie] & " te " & [oDorp] AS Aangebieddeur,
Organiseerder.OInstansie, Organiseerder.OTelefoonhuis,
Organiseerder.OTelefoonwerk, Organiseerder.OFaks, Organiseerder.OEposeie,
Organiseerder.OEposwerk, Organiseerder.OAdres1, Organiseerder.OAdres2,
Organiseerder.ODorp, Organiseerder.ODorpkode, Organiseerder.OPoskode,
Organiseerder.OPoskode, Organiseerder.OHoof, Organiseerder.Omemo FROM (Rekord
INNER JOIN Item ON Rekord.RkNo = Item.ItNommer) INNER JOIN ((Byeenkoms INNER
JOIN Organiseerder ON Byeenkoms.ByeenkomsID = Organiseerder.OrganiseerderID)
INNER JOIN (Spanne INNER JOIN (Swemmer INNER JOIN AQ13Werkpunteuit ON
(Swemmer.SwemmerID = AQ13Werkpunteuit.SwemmerID) AND (Swemmer.SwVan =
AQ13Werkpunteuit.SwVan) AND (Swemmer.SwItem = AQ13Werkpunteuit.SwItem) AND
(Swemmer.SwNaam = AQ13Werkpunteuit.SwNaam) AND (Swemmer.SwSpanID =
AQ13Werkpunteuit.SwSpanID)) ON (Spanne.SpanneID = Swemmer.SwSpanID) AND
(Spanne.SpAfkort = AQ13Werkpunteuit.SpAfkort) AND (Spanne.SpanneID =
AQ13Werkpunteuit.SwSpanID)) ON Organiseerder.OrganiseerderID =
Spanne.OrganiseerderID) ON Item.ItemID = Swemmer.SwItem
ORDER BY Item.ItNommer, Swemmer.SwItem, Swemmer.SwGalatyd,
IIf([Swopmerking]="Afw","",IIf([Swopmerking]="Dis","",[Plek]));

Johan, I am sorry this is in my home language Afrikaans. What I'll do I will
redesign the whole dbase but in I will do it in english, then it would be
better asking for help and easier for people to help

Thanks again for your effort

Kind regards>
 
F

Frank

John W. Vinson said:
Perhaps you could post the SQL view of just one of the queries that you're
expecting to show data but isn't.
Click to expand...

Query for showing results in a report
ELECT Swemmer.SwemmerID, Item.ItemID, Item.ItNommer, Rekord.RkNo,
Swemmer.SwPunte, Swemmer.SwPlek, Spanne.SpNaam, Spanne.SpAfkort,
Swemmer.SwNaam, Swemmer.SwVan, Swemmer.SwTaal, Swemmer.SwGebore,
Swemmer.SwBepaaloud, DateDiff("yyyy",[SwGebore],[SwBepaaloud]) AS Oud,
Swemmer.SwGalatyd, Swemmer.SwInskryftyd, Swemmer.SwUitdun, Swemmer.SwBaan,
Item.ItAfstand, Item.ItSwemslag, Item.ItGeslag, Item.ItGroepoud,
Rekord.RkNaam, Rekord.RVan, Rekord.RAfstand, Rekord.RSwemslag,
Rekord.RGeslag, Rekord.RGroepoud, Rekord.RTyd, Rekord.RJaartal,
Rekord.RSpnaam, Rekord.RSpAfkort, Spanne.SpInstansie, [SpkonTitel] & " " &
[SpKonNaam] & " " & [SpKonVan] AS Kontak, "Huis telefoon: " &
[SpKonTelefoonhuis] & " Werk telefoon: " & [SpKonTelefoonwerk] & "
Selfoon: " & [SpKonSelfoon] AS Kontaktelefoon, Organiseerder.OInstansie,
[ONaam] & " " & [OTitel] & " " & [Ovan] AS Orgnaam, "Huis telefoon: " &
[OTelefoonhuis] & " Werk telefoon: " & [OTelefoonwerk] & " Selfoon: "
& [OSelfoon] AS Orgtelefone, Byeenkoms.BTipe, Byeenkoms.BJaartal,
Byeenkoms.BDatum, Byeenkoms.Bgalabegin FROM ((Organiseerder INNER JOIN Spanne
ON Organiseerder.OrganiseerderID = Spanne.OrganiseerderID) INNER JOIN
Byeenkoms ON Organiseerder.OrganiseerderID = Byeenkoms.ByeenkomsID) INNER
JOIN ((Item INNER JOIN Rekord ON Item.ItemID = Rekord.OurekordID) INNER JOIN
Swemmer ON Item.ItemID = Swemmer.SwItem) ON Spanne.SpanneID =
Swemmer.SwSpanID;
Query for showing all the reesults for sending to schools
SELECT Item.ItNommer, Swemmer.SwItem, Swemmer.SwGalatyd AS Tyd,
IIf([Swopmerking]="Afw","",IIf([Swopmerking]="Dis","",[Plek])) AS Plekaandui,
Swemmer.SwemmerID, AQ13Werkpunteuit.Punte, AQ13Werkpunteuit.Plek,
IIf([Swopmerking]="Afw",0,IIf([Swopmerking]="Dis",0,IIf(IsNull([Tyd]),0,[Punte])))
AS Marks, Swemmer.Swopmerking, Item.ItAfstand, Item.ItSwemslag,
Item.ItGeslag, Item.ItGroepoud, Spanne.SpanneID, Spanne.SpNaam,
Spanne.SpAfkort, Swemmer.SwSpanID, Swemmer.SwNaam, Swemmer.SwVan,
Swemmer.SwTaal, Swemmer.SwGeslag, Swemmer.SwGebore, Swemmer.SwBepaaloud,
Swemmer.SwInskryftyd, Swemmer.SwGalatyd,
IIf([Swopmerking]="Afw","Afw",IIf([Swopmerking]="Dis","Dis",[Tyd])) AS
Reporttyd, Swemmer.SwUitdun, Swemmer.SwBaan, Swemmer.SwPunte, Swemmer.SwPlek,
Byeenkoms.BTipe, Byeenkoms.BJaartal, Byeenkoms.BDatum, Byeenkoms.BGalabegin,
Organiseerder.OInstansie, Rekord.RkNaam, Rekord.RVan, Rekord.RAfstand,
Rekord.RSwemslag, Rekord.RGeslag, Rekord.RGroepoud, Rekord.RTyd,
Rekord.RJaartal, Rekord.RSpnaam, Rekord.RSpAfkort, [ONaam] & " " & [OTitel] &
" " & [Ovan] AS Orgnaam, "Huis telefoon: " & [OTelefoonhuis] & " Werk
telefoon: " & [OTelefoonwerk] & " Selfoon: " & [OSelfoon] AS
Orgtelefone, [Oinstansie] & " te " & [oDorp] AS Aangebieddeur,
Organiseerder.OInstansie, Organiseerder.OTelefoonhuis,
Organiseerder.OTelefoonwerk, Organiseerder.OFaks, Organiseerder.OEposeie,
Organiseerder.OEposwerk, Organiseerder.OAdres1, Organiseerder.OAdres2,
Organiseerder.ODorp, Organiseerder.ODorpkode, Organiseerder.OPoskode,
Organiseerder.OPoskode, Organiseerder.OHoof, Organiseerder.Omemo FROM (Rekord
INNER JOIN Item ON Rekord.RkNo = Item.ItNommer) INNER JOIN ((Byeenkoms INNER
JOIN Organiseerder ON Byeenkoms.ByeenkomsID = Organiseerder.OrganiseerderID)
INNER JOIN (Spanne INNER JOIN (Swemmer INNER JOIN AQ13Werkpunteuit ON
(Swemmer.SwemmerID = AQ13Werkpunteuit.SwemmerID) AND (Swemmer.SwVan =
AQ13Werkpunteuit.SwVan) AND (Swemmer.SwItem = AQ13Werkpunteuit.SwItem) AND
(Swemmer.SwNaam = AQ13Werkpunteuit.SwNaam) AND (Swemmer.SwSpanID =
AQ13Werkpunteuit.SwSpanID)) ON (Spanne.SpanneID = Swemmer.SwSpanID) AND
(Spanne.SpAfkort = AQ13Werkpunteuit.SpAfkort) AND (Spanne.SpanneID =
AQ13Werkpunteuit.SwSpanID)) ON Organiseerder.OrganiseerderID =
Spanne.OrganiseerderID) ON Item.ItemID = Swemmer.SwItem
ORDER BY Item.ItNommer, Swemmer.SwItem, Swemmer.SwGalatyd,
IIf([Swopmerking]="Afw","",IIf([Swopmerking]="Dis","",[Plek]));

Johan, I am sorry this is in my home language Afrikaans. What I'll do I will
redesign the whole dbase but in I will do it in english, then it would be
better asking for help and easier for people to help

Thanks again for your effort

Kind regards>
 
J

John W. Vinson

FROM (Rekord
INNER JOIN Item ON Rekord.RkNo = Item.ItNommer) INNER JOIN ((Byeenkoms INNER
JOIN Organiseerder ON Byeenkoms.ByeenkomsID = Organiseerder.OrganiseerderID)
INNER JOIN (Spanne INNER JOIN (Swemmer INNER JOIN AQ13Werkpunteuit ON
(Swemmer.SwemmerID = AQ13Werkpunteuit.SwemmerID) AND (Swemmer.SwVan =
AQ13Werkpunteuit.SwVan) AND (Swemmer.SwItem = AQ13Werkpunteuit.SwItem) AND
(Swemmer.SwNaam = AQ13Werkpunteuit.SwNaam) AND (Swemmer.SwSpanID =
AQ13Werkpunteuit.SwSpanID)) ON (Spanne.SpanneID = Swemmer.SwSpanID) AND
(Spanne.SpAfkort = AQ13Werkpunteuit.SpAfkort) AND (Spanne.SpanneID =
AQ13Werkpunteuit.SwSpanID)) ON Organiseerder.OrganiseerderID =
Spanne.OrganiseerderID) ON Item.ItemID = Swemmer.SwItem
ORDER BY Item.ItNommer, Swemmer.SwItem, Swemmer.SwGalatyd,
IIf([Swopmerking]="Afw"
Click to expand...

If ANY of these tables - Rekord, Item, Byeenkoms, Organiseerder, Spanne,
Swemmer, AQ13Werpunteuit - is empty, then this query will return NOTHING.
There must be matching records on all of the joins; if there are no records
then there is no match, and therefore the query will retrieve no records.

You can use Outer Joins - LEFT JOIN or RIGHT JOIN instead of INNER JOIN - if
it's appropriate to display data from one table even if there is no match in
some other table.
 
J

John W. Vinson

FROM (Rekord
INNER JOIN Item ON Rekord.RkNo = Item.ItNommer) INNER JOIN ((Byeenkoms INNER
JOIN Organiseerder ON Byeenkoms.ByeenkomsID = Organiseerder.OrganiseerderID)
INNER JOIN (Spanne INNER JOIN (Swemmer INNER JOIN AQ13Werkpunteuit ON
(Swemmer.SwemmerID = AQ13Werkpunteuit.SwemmerID) AND (Swemmer.SwVan =
AQ13Werkpunteuit.SwVan) AND (Swemmer.SwItem = AQ13Werkpunteuit.SwItem) AND
(Swemmer.SwNaam = AQ13Werkpunteuit.SwNaam) AND (Swemmer.SwSpanID =
AQ13Werkpunteuit.SwSpanID)) ON (Spanne.SpanneID = Swemmer.SwSpanID) AND
(Spanne.SpAfkort = AQ13Werkpunteuit.SpAfkort) AND (Spanne.SpanneID =
AQ13Werkpunteuit.SwSpanID)) ON Organiseerder.OrganiseerderID =
Spanne.OrganiseerderID) ON Item.ItemID = Swemmer.SwItem
ORDER BY Item.ItNommer, Swemmer.SwItem, Swemmer.SwGalatyd,
IIf([Swopmerking]="Afw"
Click to expand...

If ANY of these tables - Rekord, Item, Byeenkoms, Organiseerder, Spanne,
Swemmer, AQ13Werpunteuit - is empty, then this query will return NOTHING.
There must be matching records on all of the joins; if there are no records
then there is no match, and therefore the query will retrieve no records.

You can use Outer Joins - LEFT JOIN or RIGHT JOIN instead of INNER JOIN - if
it's appropriate to display data from one table even if there is no match in
some other table.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

deleting records 0
How to default CurUser() into a field in a new record in a Select 4
Delete Multiple records 2
Delete some records 7
Can someone please HELP! 2
Problem with deleting record 5
Deleting duplicate records 3
Re: Pass field value selected in Combo Box to SQL Query 1

Top