Data Entry/Import - Advice?

[shepHeard]

Hi There,

I'm building a database for a friend's company.

My query is really to do with the best way to add new records to their database.

The database has four tables:

[Species] which is linked to multiple loci in the [Locus] table. For each Locus their are multiple alleles in the [Allele] table. Additionally, there is the [Reference] table, each reference can be linked to one or more loci in [Locus].

Users could add a new species, it's loci, the alleles for each locus and select the reference for each locus from a list (or add a new reference) using the Form used to view the data. However, this is very time-consuming when there are a large number of species to add but is easiest for me, as it requires little or no extra coding.

The other way I was thinking, would be to do a kind of batch update from an excel file. This would suit the users better as their data are already in this format.

The problem is that I guess I can't do a simple import spreadsheet due to the one-to-many relationships, as there would be no primary/foriegn keys in the excel sheet.

Would the best way around this be to add the first species, then for this species add the first locus and its alleles, then the next locus and so on.. then the next species? This way I could use the keys as they are generated..

Alternatively, I could get excel to generate the keys, and query the Access database to make sure it is not generating keys already in use. Then I can do a more simple import procedure...

I can do either using VB. Which do you think would be 'best'? Or should I just tell the users they'll have to enter stuff by hand the long way?!

Cheers

 

[TTime]

My intial sugestion = Import or Link to the existing Excel Worksheet(s). The reuslting table could then be adapted - (perhaps using a Make Table Query?) to suit. This tends to save a lot of "creating structure and entering data" work. HTH. Bernard

 

[shepHeard]

Thanks for the reply Bernard,

The problam with linking/importing is that I can't see a way to generate the primary keys. They're generated as the data are input, and obviously I need to make sure the tables are linked correctly in the one-to-many-to-many relationship I basically have with my tables..

The problem I suppose is that the work sheet will effectively represent the flattened (is that the right word?) database - i.e. for each speacies - many loci, for each locus, many alleles.

So, in column A I may have the same species name repeated 30 times, colum B may have say 10 copies of locus 1, 5 copies of locus 2, etc and Colum C will have the individual alleles.

I've attached an example of how the excel sheet may look.

Basically, these data would need to be appended to existing tables in the database..

 

[TTime]

I've had a shot at creating the various tables from your excel sheet. While doing this the one issue which immediately became clear was who\when was the information recorded. I think this might be the key table for you to consider.
e.g. tblObservations.
Data would have a unique (Key) record field,
a date field
and possibly a name for the person who recorded the information?

This could then provide the link through to the same field in the other tables (via table relationships)?
Please excuse me if I am way-off-beam. HTH. Bernard

 

[shepHeard]

Ahh. Sorry, maybe I didn't make it clear - I've already got the database..

I've attached it here. (There is another part to it, but I havn't icluded it here.)

Anyway, as you'll see from the tables (you'll need to hold shift as you open it), they're all linked via different keys. My issue is how to append data from a spreadsheet to these tables, while preserving the keys (and relationships etc).

(Don't worry about the References table for now!)

Really appreciate the help though, Cheers!

 

[TTime]

It appears you are ahead of me in terms of database design. However the following might be useful.(From Microsoft)
ACC2000: How to Use an Append Query to Set Initial Value of an AutoNumber Field. The web link =
http://support.microsoft.com/kb/209696
HTH Bernard

 

[shepHeard]

Ha! Cheers for the compliment.. though it certainly doesn't feel that way at the moment!

I think I'm going to go down the root of scanning and inputting the data from a worksheet one row at a time, taking the keys as they're created.. It just seems a bit of a messy way to do it that's all - like I'm using my VB knowledge to make up for my lack of DB skills!

 

[Kelemit]

Are you trying to do a one time thing? Or a system for easy imports many times during the life of your database for the user type thing?

Kelemit

 

[shepHeard]

Hi Kelemit,

It would be a system for imports several times. Basically, editing and addition to existing records would probably be done via the user form.

However, data sets (new records) tend to be completed in batches and stored in excel, these data would then need to be added in a batch as and when they're finished.

I'm envisioning an excel "template" workbook, with data in a specific layout.. (see attchment above somewhere).

Cheers for any advice!

PS - as for the keys, I've realised I can use a random number generator to generate them (and check they're not in use) at the point of input...