4

I have an Access database. Let's pretend it's for a pet store.

There's a table for animals.

Animals (animal_id, birth_date, price)

And then specific tables for the different types of animals we sell.

Dogs (animal_id, bark_volume)
Cats (animal_id, collar_size, shedding_rate)
Fish (animal_id)

Fish aren't interesting so they don't have any special fields. The Fish table just exists so you know which records in the Animals table are fish.

Now, I have a general purpose form for adding animals to the pet store. Before you get the form, you first have to say what kind of animal you're adding. Based on that, the form shows/hides fields, changes its recordsource, and binds the fields to the appropriate data columns. The form pulls its data from the queries DogInfo, CatInfo, and FishInfo.

Now, when you enter a dog or a cat, everything is fine. A record is added to both Animals and either Dogs or Cats.

However, when you enter a Fish, all you get is an Animal, no Fish.

What could be causing this? Is it somehow caused by the lack of other columns on the Fish table?

(Let's leave aside the fact that updating tables with a select query makes no sense at all. I didn't expect Access to let me do it at all in the first place, but Access's motto seems to be "Make the wrong thing easy to do and the right thing awkward to do." The database is relatively simple and infrequently used, though, and it's at least 100 times better than it was before I started working on it, so I'm not really too worried about this issue as long as I can make it work.)

Instance Hunter
  • 7,621
  • 5
  • 39
  • 52
  • There is quite a lot wrong with this table set-up. I hope it is only an example that has not been carefully devised. – Fionnuala Apr 14 '09 at 12:56
  • The basic design is based off this: http://stackoverflow.com/questions/554522/something-like-inheritance-in-database-design/554681#554681 – Instance Hunter Apr 14 '09 at 13:06
  • There is much wrong with this, search google for 'access table structure'. – zuk1 Apr 23 '09 at 12:37
  • 1
    Unless you have a specific comment or something more useful than googling a term with no useful results, you're not being helpful. – Instance Hunter Apr 24 '09 at 14:33

4 Answers4

7

"Is it somehow caused by the lack of other columns on the Fish table?"

Yes - when you enter data on child records (Dogs and Cats) Access will automatically fill in the parent ID (animal_id)

Since there is no data entry for the fish record it does not get created. You have to do that in code. Not sure how your form and data source is setup but you would do something like this on one of the form events:

Fish![animal_id] = Animal![animal_id]

Edit

In your FishInfo query you must give the Fish.[animal_id] an alias - you can't have two fields with the same name - call it Fish_animal_id

Then in the Form_BeforeUpdate event put this:

Me.Fish_animal_id = Me.animal_id
DJ.
  • 15,477
  • 3
  • 39
  • 45
  • This sounds right, but I can't get the code to run. It tells me something like "qualifier must be a collection." – Instance Hunter Apr 17 '09 at 12:25
  • Well it depends on the recordset(s) / field names you are using - basically you have to set the animal_id of the Fish record to the main animal_id – DJ. Apr 17 '09 at 15:05
  • I adjusted the field names to correspond, but Access still doesn't like it. – Instance Hunter Apr 18 '09 at 04:44
  • Would need more info on the code used to change the recordsource and the field names, etc. – DJ. Apr 19 '09 at 21:47
  • The data source is set in a Select Case block which checks the value of enum and then sets the RecordSource property of the form to be the name of the relevent query as a string. So far, this is still not working, so even though StackOverflow does, I don't consider this answered. – Instance Hunter Apr 24 '09 at 14:43
  • I'm willing to help - you just haven't responded in days - So you have a FishInfo query? Please post the names of the fields in that query. thx – DJ. Apr 24 '09 at 15:38
  • The FishInfo query is set up with the Animals and Fish tables joined on animal_id (all rows from Fish, only rows from Animals with matching id... this is how Fish are identified as being fish) and only the fields from Animals (all of them). – Instance Hunter Apr 24 '09 at 20:50
  • It ended up having to be Me!Fish_animal_id and Me!animal_id, and I had to make sure that Fish_animal_id was selected in the query, but... IT WORKED! Thank you. I wish I could retroactively give you the full bounty, but alas, I cannot. – Instance Hunter Apr 29 '09 at 13:59
2

Have you thought about configuring relationships on the different tables? Given the design above, I would start by adding an identifying column to the specific-animal tables, and setting it as the primary key. E.g.:

Dogs(DOG_ID, animal_id, bark_volume)
Cats(CAT_ID, animal_id, collar_size, shedding_rate)

etc. In the relationships view, you'd then define a one-to-many (one-to-one?) relationship from Animals.ANIMAL_ID to Dogs.animal_id. You could then hook up the Animals table to a combo/listbox control on your form to select a record from that table. I think if you configure the control correctly, you can even create new records from that control (or you could use a subform).

alastairs
  • 6,419
  • 8
  • 47
  • 61
0

Do you not have an separate IDs for the Dogs/Cats/Fish tables? Assuming the only difference is the number of columns, I'd be curious if that suddenly fixed it.

BIBD
  • 14,299
  • 24
  • 78
  • 130
0

Bad design aside, did you set up a relationship between the various tables? And did you set the tables to enforce referential integrity?

AnonJr
  • 2,681
  • 1
  • 25
  • 39