Access database and new database table inconsistencies.

[bethpreston]

I've been going through the tables/views in the two databases to look for any differences. We'd like to keep them as consistent as possible, at least to start with during the transition period. Some of the tables only have minor differences like different column names, but others have missing columns, or other differences which might indicate underlying differences in the data between the two databases. Once some of these more obvious differences are resolved we can run more complex analyses on the two databases and see if they give the same results.

The differences: Life history table:

• Start.end column and Prev.Name column are missing
• Life history codes are in lower case rather than upper case
• Different number of rows of data - unsure why? Where are the discrepancies coming from? Are litter code events included?

Babysitting table:

• Time_start column has mistakes, times like 16.05 or 17.05 are in the new database table as 16.50 or 17.50. (perhaps an issue with time format?)
• Column names are lower case in the new database (upper case in Access)

Antiparasite experiment:

• Date entered column is missing

Captures new 2013:

• Different number of rows in the two tables ~400 different - unsure why (some blank records in access but not enough to explain this)

DNA:

• Date entered column is missing

Group composition:

• Date entered column is missing

HPA samples:

• Date entered column is missing

MCE females:

• Date created column is missing

MCE litters:

• Column names are different
• Litter birth data and date entered columns are missing
• Date formats are different (may also be the case in other tables - POSIXcT format would be best if possible)

MCE provisioning:

• Column names are different

Oestrus:

• Different number of rows (unsure why)
• Time column and changes to data column are missing
• Copulation column does not seem to match between the two versions

Meteorological data:

• Column name RAIN_MWEY should be RAIN_MWEYA
• Date entered column missing

Ox shielding experiment - feeding:

• Different column names

Ox shielding experiment - females:

• Different column names

Ox shielding experiment - males:

• Different column names

Poo database:

• Different column names
• 2 extra rows in access database (no obvious blanks etc.)

Pups:

• Missing columns - Session; Editing comments; Date entered

Radio:

• Date entered column missing

Ultrasound:

• Different structure to the table - each foetus has its own row, rather than each pregnancy having a row (as in original) - some data might have been lost here.

Weights:

• Missing columns - collar weight, lat/long, date entered
• What's the app doing in terms of the collar weights?
• Are the collar weights taken off automatically? Some comments show that the collar weight has already been removed from the total.

[bethpreston]

Column name differences:

Oxidative shielding experiment feeding - AM/PM, Amount of egg, Female ID

Oxidative shielding experiment females - Pack, Treatment, Group

Oxidative shielding experiment males - Pack, Date start, Date stop

Poo database - Capitals versus not for all columns

[aidanw]

I think I have made all these changes!

Remaining issues are

LifeHistory: The 'previous names' column sometimes seems to have individual names and sometimes pack names. I cannot see a way of automatically knowing which are which but maybe with discussion we can work something out.

Babysitting: There are some records where time start and time end appear reversed. We should look at some and decide if they can just be swapped or is there some good reason.

Oestrus In the copulation column there are sometimes multiple individuals. I am not sure how to deal with this.

pup association, editing comments. This data is recorded in the new database, I have just merged it with the comments field. I could add a new column if you would prefer but I was reluctant to add what felt like a 'temporary' column.