Deleting records from only one table

[coalchamber]

Hello all,

I am in a state of confusing.

I am creating a small database to be used to track who has borrowed
what dvds from me. So far, i have 3 tables:

1. DVD collection
2. Customer
3. Borrow Table

My plan is to have all customers in the customer table, all dvds in
the dvd collection table and create a record in the borrow table when
ever one borrows a dvd simply by recording the customerID and DVDID.
When it is returned i will delete that record so that dvd is no long
seen as being borrowed.

I have made a one to many relationship between the primary key
(customerID) to the customer ID column in the borrower table. And a
one to one from the DVDID in the dvd collection table to the DVD id
column in the borrow table (since at home a person will only have 1
copy of a dvd).

Here comes the problem. I have used the built in commend button that
deletes a records from a table. Here i created a form that views all
records in the borrow table and when the record is found which
contains the data of the dvd jsut returned i press the button to
delete that record. Sadly, it will no only delete the record in the
borrow table, it will delete the record in the DVD collection table.

Is there a way around this? Is there a way in which i can write a
query to delete just that record? I was looking at the code on the
click event for the button but it is alittle over the top for me. I
would prefer not to change the design because i have based other
things off this design (reports, new borrowings etc) but if it comes
to it i will.

I hope i have explain this correctly, and i help i am understood

 

[Allen Browne]

Use a form with a subform.

The form is bound to the Customer table.
The subform is bound to the Borrow table.
You can now delete the record in the subform, without deleting the Customer
in the main form.

 

[Al Campagna]

Sounds like you have Cascading Deletes turned on, with the Borrow table linked as
Master... to the DVD table. Delete a record in Borrow, and Cascade deletes the DVD from
the "related" child table.

That brings up two issues...
First the table relations. One Customer to Many Borrows (see later on), with the DVD
table acting just as a "value" provider to the Borrow table. DVD to Borrow really doesn't
need a relationship. You manually enter the DVDID in each borrow record, and once the
borrow record has that DVDID, you can then use that field to relate back to the DVD table
in any subsequent form, or query, or report. Say, for example, your product had a color
selection, or a zipcode, or a size selection... those lookup tables wouldn't need to be
"related" to the product Borrow table either... they just provide informational values.

Second point is the fact that you are deleting Borrow records. The whole purpose of
databases vs spreadsheets, is the ease of keeping "historical" information, and more
importantly... being able to interpret that historical data to your business advantage.
I would ceratainly want to keep a historical record of who, what, when borrowed, and
when returned on all transactions. I noticed that you said something like "a customer
will only borrow one DVD at a time." Well, that's fine, but that concept doesn't mean
you'll only need one Borrow record at a time... per customer."
You're not using the power of a relational database... you have, basically, a
spreadsheet. Could you tell me how many DVDs Mr Smith has borrowed from you in the last
year. Perhaps you might like to offer 1 free borrow for every 10. etc.. etc..

Now, I realize that you may just be doing this on a "personal" basis (not as a
business), but even so, it's always worthwhile to design your system properly.

**You've already done the "heavy lifting" building the app... don't throw away those
Borrow records when there's no need to.

Adding a DateOut and DateIn field to your Borrow table, where... DateIn (having a
value) would indicate the DVD has been returned. No DateIn... the DVD is still out.

--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."


Since this is an application to track borrowing, I'm not sure why you're deleteing any
records from the Borrow table

 

[Al Campagna]

Hi Allen,
I think the OP is deleting the Borrow record, and the the DVD that was borrowed is
being deleted from the DVD table.
--
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

 

[coalchamber]

Hi Allen,
I think the OP is deleting the Borrow record, and the the DVD that was borrowed is
being deleted from the DVD table.
--
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVPhttp://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."







- Show quoted text -

Ok, thank you all for writing. i will explain alittle futher.

My uncle currently has well over 1000 dvds and for a school project i
am desiging it for him as my client. He does not wish to have any
records, all he cares about is just knowing where they all are at any
time. This is the reason whjy i was wanting to delete the borrow
record while keeping the DVD in the other table.

This is the strange thing, the enforce referential integrity is not
turned on at all. This is partly why i have been confused.

I will try and create a subform but let the truth be known i am not
pro aat access. I have been trying to edit a query for the onclick
event of the command button but, again, i do not have the knowledge
required.

 

[AccessVandal via AccessMonster.com]

Hi,

I would go by Allen’s method. Allen’s method is much simpler to use. You
don’t need cascade deletes at all. Three tables are good enough. However, for
the subform, you’ll need the bound the form’s record source to a SQL string.

Here’s a generic syntax. (Create this if you want to view the DVDTitle name,
else just bound the Borrow Table as per Allen’s solution.)

SELECT [Borrow Table].[BorrowerID], [DVD Collection].[DVDCode], [DVD
Collection].[DVDTitle], Col1, Col2,….
FROM [DVD Colllection], [Customer], [Borrow Table]
WHERE [Borrow Table].[BorrowerID] = [Customer].[CustomerID]
AND [Borrow Table].[DVDCode] = [DVD Collection].[DVDCode]

If you don’t know how to create the correct syntax, use the Query Grid Editor.
Populate all three tables and the necessary fields into the editor.

Once the fields are done, right click on the empty part of the editor (near
the Tables).
Click on “SQL Viewâ€, select the SQL string and copy the syntax and paste into
the subform record source.

Or just name this Query to something like “qryBorrowTable†then key in this
name into the subform record source.

And you’re done.

 

[Al Campagna]

The Borrow table would go in a subform on the Customer Main form.

The main form is based (via a query against Customers table) as the form's
RecordSource)... with say a CustID as a unique key field.
The subform (can be a single form (not continuous)) will use a query against the Borrow
table (with it's CustID field) as it's RecordSource.
The Parent main form (CustID) will be linked to the Child subform (CustID), and there
should be a one to many relationship between those two tables.

Basically, the DVD table would only be used to help select a Borrowed DVD in the subform.
I'd use a combo box based on the DVD table to allow selction of any DVD... except those
that are Borrowed at the time.
I'd at least add a DateOut field to Borrow. Seems to me you'd wnat to know if a DVD has
not been returned within some reasonable time. Yoy call...

--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

 

[coalchamber]

The Borrow table would go in a subform on the Customer Main form.

The main form is based (via a query against Customers table) as the form's
RecordSource)... with say a CustID as a unique key field.
The subform (can be a single form (not continuous)) will use a query against the Borrow
table (with it's CustID field) as it's RecordSource.
The Parent main form (CustID) will be linked to the Child subform (CustID), and there
should be a one to many relationship between those two tables.

Basically, the DVD table would only be used to help select a Borrowed DVD in the subform.
I'd use a combo box based on the DVD table to allow selction of any DVD... except those
that are Borrowed at the time.
I'd at least add a DateOut field to Borrow. Seems to me you'd wnat to know if a DVD has
not been returned within some reasonable time. Yoy call...

--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVPhttp://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

Ok, I managed to get part way there, but being the novice i am i think
i may have strayed from your suggestions a little.

First i created a query that looks at everyone in the booking table
and their dvds. I made up a form and sub form based on this. Now i
will find the customer and the dvd's they have borrowed are in the
subform. Next to each one is a delete button. I have made up a delete
query and once the button is pressed the delete query runs. It will
ask for the DVD ID to delete, i enter it, and it deletes only that
record not touching the DVD collection table which is great!

The query is [borrower table Query]![DVD ID] and it is based on the
borrower table (i think i have said it right).

But now the problem is how would i make it so it does not ask for the
DVD ID? Is there a way that i can put it in the query? If i am being
hard to follow i can very easily upload it somewhere so you guys can
follow.

Again, thank you everyone who has said something, but some of the
things you are saying are going over my head, sorry if that is a
bother......

 

[onedaywhen]

The whole purpose of
databases vs spreadsheets, is the ease of keeping "historical" information, and more
importantly... being able to interpret that historical data to your business advantage.

You're not using the power of a relational database... you have, basically, a
spreadsheet. Could you tell me how many DVDs Mr Smith has borrowed from you in the last
year.

For me, neither the 'power' nor the 'purpose' lies with the ability of
keeping historical records, which you can also do in a spreadsheet;
rather it is data integrity.

[Access/Jet is a file system with a SQL gateway, impressive stuff but
hardly anyone's definition of a 'relational database' -- can we agreed
on 'SQL DBMS', please?]

My impression is that most data in Access is 'current state' e.g. the
Employees table tend to have columns for an employees' current name
rather than having a separate table EmployeeNamesHistory with periods
where each name was in effect. In fact, valid-time state tables
require table-level constraints in Jet engine -- the aforementioned
data integrity -- which are not exposed to the Access user interface,
hence are unfamiliar to most 'power' users.

Jamie.

--

 

[coalchamber]

The whole purpose of
databases vs spreadsheets, is the ease of keeping "historical" information, and more
importantly... being able to interpret that historical data to your business advantage.

You're not using the power of a relational database... you have, basically, a
spreadsheet. Could you tell me how many DVDs Mr Smith has borrowed from you in the last
year.

For me, neither the 'power' nor the 'purpose' lies with the ability of
keeping historical records, which you can also do in a spreadsheet;
rather it is data integrity.

[Access/Jet is a file system with a SQL gateway, impressive stuff but
hardly anyone's definition of a 'relational database' -- can we agreed
on 'SQL DBMS', please?]

My impression is that most data in Access is 'current state' e.g. the
Employees table tend to have columns for an employees' current name
rather than having a separate table EmployeeNamesHistory with periods
where each name was in effect. In fact, valid-time state tables
require table-level constraints in Jet engine -- the aforementioned
data integrity -- which are not exposed to the Access user interface,
hence are unfamiliar to most 'power' users.

Jamie.

--

Thank you everyone for your help but i managed to write a different
delete query and used a macro connected to a button. The delete query
confused the hell out of me, but i eventually figure out that i wasn't
referencing it through the main form, then the subform. Silly me.
Anywhose it works!! Thanks all again.