Multiple Values, Different Quantities?!

[Swilliams987]

Hello all! I have designed a database that I'm pretty proud of- and for the most part it's functional, but I have one slight problem. . .

This database's purpose is to track the importation of wild animals into our state. Currently, my main form has a subform where I would choose the species name being imported and the quantity being imported from a combo box. So far, so good.

But what do you do if more than one species is being imported?

For example:
Gorilla 25 (quantity)
Capuchin 10

I am having no problem with entering one species with one quantity, but I can't figure out how to enter two species and two quantities. I tried the mulitple value field and the problem there was that I couldn't decipher how many of each species were being imported. I tried adding a separate field called "species 2" and "quantity 2"- but then when it came down to designing a query I wasn't able to merge species 2 with species 1 to get the overall results.

Anyone that could help me would sure be doing me a great favor! Thanks a lot, I appreciate it!

[pbaldy]

What is your table structure? Generally, what you describe would be handled by having two records, one for gorillas and one for capuchins. Like a sales database would have a record for each item sold on an invoice (product and quantity).

[Swilliams987]

These are the relevant tables:

tblPermitInformation:
atnPermitNumber
strConsigneeFacilityName
strConsignorFacilityName
dtmSpeciesArrivalDate
dtmApplicationDate
dtmPermitIssueDate
strSpeciesName
strQuantity

tblSpeciesID&Name:
atnSpeciesID
strSpeciesName

[pbaldy]

Well, two things. First, the species and quantity should probably be in a separate table along with I suspect the permit number. The two tables would be related one to many on that number. That allows you to have any number of species on a given permit. The structure is conceptually similar to a standard invoicing setup, where an invoice header table would contain the customer, date, etc, and a details table would contain a record for each product purchased.

Second, in that related table you should be storing the species ID, not the name. That would be normal, else what's the purpose of the ID field?

[Swilliams987]

Right now the purpose of the Species ID and Name table is to list all the species that could be imported. The ID just serves as the primary key to the table in autonumber format- there isn't any other purpose for the ID.

So, something like this?

tblPermitInformation:
atnPermitNumber
strConsigneeFacilityName
strConsignorFacilityName
dtmSpeciesArrivalDate
dtmApplicationDate
dtmPermitIssueDate
strSpeciesName
strQuantity

tblSpeciesID&Name: (ELIMINATE?)
atnSpeciesID
strSpeciesName

tblSpecies:
atnSpeciesID (for primary key purposes)
strSpeciesName

tblQuantity:
atnQuantityID (for primary key purposes)
numQuantity

[pbaldy]

No, tblSpeciesID&Name is fine (except for the symbol in the name, which is frowned upon along with spaces). What I meant was (following your conventions):

tblPermitInformation:
atnPermitNumber
strConsigneeFacilityName
strConsignorFacilityName
dtmSpeciesArrivalDate
dtmApplicationDate
dtmPermitIssueDate
strSpeciesName delete from this table
strQuantity delete from this table

New table "tblSpeciesImported" (or a name of your choosing)
atnSpeciesImportedID (primary key)
lngPermitNumber (foreign key from tblPermitInformation, related one-to-many)
lngSpeciesID (foreign key from tblSpeciesID&Name, related one-to-many)
strQuantity (I would expect this to be a numeric data type)

[Swilliams987]

This works really well! Thank you so much! Now, if I could just get your guidance on one more thing. . .

I have created a form using tblSpeciesImported call sfrmSpeciesImported. I have inserted sfrmSpeciesImported as a subform into my main form. So far so good. If I want the user to have the option of choosing another species to be imported with a different quantity, how would that best be handeled? Right now I have just inserted an additional subform of sfrmSpeciesImported on my main form, but it would be great if I could add a button with a macro that would open the the form in pop up if I wanted to input more species.

So I tried it. I tried it with the same subform- sfrmSpeciesImported and it didn't record the same permit number- the permit number was blank on tblSpeciesImported. So then I created sfrmSpeciesImportedAddAnother as a pop up and it also didn't record the same permit number. Is there a way to make this work where it will record the same permit number?

Thanks again for your help! I truly appreciate it!

[pbaldy]

I would have the subform in continuous forms view, which should enable you to add as many species as you want (each as a separate record). You should only have one subform for tblSpeciesImported .

The form and subform are normally kept in sync with the master/child link properties of the subform control. If you used the wizard, it should have asked about linking fields.

[Swilliams987]

Great, thank you. I haven't worked with continuous forms yet. When I select the species name and quantity, another combo box appears below. Is there a way to limit the continuous form to, let's say, two records?

[pbaldy]

Not natively, unless the new data macros can do it. I haven't worked a lot with 2010 yet. You would need to use code. A brief test shows this to work in the current event:

  If Me.RecordsetClone.RecordCount > 1 Then
    Me.AllowAdditions = False
  Else
    Me.AllowAdditions = True
  End If

[Swilliams987]

Sorry I am writing this so late after your last response, but I wanted to thank you for helping me with this.

[pbaldy]

No problem, I hope it worked for you.