Using a query to delete values in a table?

[fiel]

I have a form that will update a query for StaffInformation, this query has data for: name, worker ID, wage, and phone number. For my tables, I have set the worker's ID number for the primary key and as the only field in one table with a 1 to many relationship to a second table that holds all the data for that person. I have no problem adding new workers, but when I delete workers from my query, everything will delete as I want it to except for the Worker's ID. Any ideas?

 

[fiel]

To be a little more clear about the deleting part, all entries in the query will be deleted, as all the data in the data table holding all worker information. The part that doesn't delete is the single table I have where the ID number is the primary key and the only field, the number doesn't delete.

 

[randy700]

How about...

Set relationships with cascade delete. When you delete from the "single field" table, all data from the related table will also be deleted.

The query you need would be something like...

[blue]DELETE * FROM tblYourTable WHERE ID = [/blue][i][red]value[/red][/i]

Randy

 

[fiel]

I set the relationship with cascade delete. But the problem is the "single field" is the only thing that doesn't delete.

 

[PHV]

I set the relationship with cascade delete
So, deleting only the "single field" should delete all the childs.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[JoeAtWork]

The record you should be deleting should be on the ONE side, sounds like you are deleting a record on the MANY side.

I suspect you really only need one table (are there really workers who have more than one job and wage?):

Workers
WorkerID
Name
Wage
PhoneNumber

But if there really is a need for two tables, I would think you would have more than just a primary key in the ONE table, something like:
[tt]
Workers Jobs
WorkerID------------| JobID
Name |---------WorkerID
PhoneNumber Wage
[/tt]

 

[fiel]

It's more like such:

WorkerID------|
|------WorkerID
Name
Wage
ID

The information in the childs delete as I need, but my main retains the WorkerID.

The only way everything gets deleted is if I go straight to the single field table and delete the value.

 

[PHV]

go straight to the single field table and delete the value
This is precisely what I've suggested you 11 Jul 07 14:03

 

[fiel]

The thing is I wanted to be able to do it through a form.

JoeAtWork: I've tried his suggestion regarding an uncertainty for multiple fields. There is only 1 set of data per each WorkerID. I have tried redoing my tables so that I only have 1 table of information. In doing so I set the WorkerID as the primary key so that there will never be duplicates of the same WorkerID.

After putting this all together on the form, my data is adjusting as I need it. I'm just not sure if this is the proper way to do things; I always thought you had to create a seperate table for one category so you can 'go into it' and see all relating data?

 

[PHV]

Have a look here:

http://r937.com/relational.html

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[JoeAtWork]

You have a MANY table if there can be multiple records related to the ONE table. In your case however, each record was complete by itself - one worker with one name with one phone number and one wage. If the worker has the possibility of having "more than one" of something, that's where it would go in a MANY table.

Let's say you give bonuses to workers based on their sales. You might have a Bonuses table that has the fields WorkerID (foreign key to Workers table) and BonusAmount. Because a worker might have more than one bonus, there is a one-to-many relationship between Workers-to-Bonuses.