Database Dev't

DanielWaktola — Tue, 05 Jan 2016 22:13:56 GMT

I'm in need of hints about the database design on a small study I'm currently conducting on.

Here are the specifications of the database which is stored in Excel file

• Number of data collection sites: 11 (from ID_1 up to ID_11)

• Number of data collection days: 4 days (April 7, April 8, April 9, April 11)

• Time of data collection in each day: 6 times (9:00 am, 12:00 pm, 3:00 pm, 6:00 pm, 9:00 pm; 12:00 am)

• Types (categories) of data collected at each time: 4 types (A, B, C, D)

What I did was, save the excel file in CSV, import it to the ArcMap 10.1; and Join it to the layer which consists the sample site locations. However, when I checked the merged Attribute table, not all the data was imported. I'm suspecting that the type of my data base is not compatible. I need your suggestions. Below, I have appended the database I created in Excel/CSV format.

ID Date Time DataType Frequency

01 7-Apr 9:00 AM A 0

01 7-Apr 9:00 AM B 8

01 7-Apr 9:00 AM C 4

01 7-Apr 9:00 AM D 2

01 7-Apr 12:00 PM A 0

01 7-Apr 12:00 PM B 8

01 7-Apr 12:00 PM C 3

01 7-Apr 12:00 PM D 0

01 7-Apr 3:00 PM A 0

01 7-Apr 3:00 PM B 3

01 7-Apr 3:00 PM C 6

01 7-Apr 3:00 PM D 1

01 7-Apr 6:00 PM A 0

01 7-Apr 6:00 PM B 8

01 7-Apr 6:00 PM C 6

01 7-Apr 6:00 PM D 1

01 7-Apr 9:00 PM A 0

01 7-Apr 9:00 PM B 6

01 7-Apr 9:00 PM C 5

01 7-Apr 9:00 PM D 2

02 7-Apr 9:00 AM A 0

02 7-Apr 9:00 AM B 5

02 7-Apr 9:00 AM C 6

02 7-Apr 9:00 AM D 0

02 7-Apr 12:00 PM A 0

02 7-Apr 12:00 PM B 8

02 7-Apr 12:00 PM C 8

02 7-Apr 12:00 PM D 0

02 7-Apr 3:00 PM A 0

02 7-Apr 3:00 PM B 8

02 7-Apr 3:00 PM C 7

02 7-Apr 3:00 PM D 4

02 7-Apr 6:00 PM A 0

02 7-Apr 6:00 PM B 7

02 7-Apr 6:00 PM C 8

02 7-Apr 6:00 PM D 1

02 7-Apr 9:00 PM A 0

02 7-Apr 9:00 PM B 8

02 7-Apr 9:00 PM C 5

02 7-Apr 9:00 PM D 0

03 7-Apr 9:00 AM A 0

03 7-Apr 9:00 AM B 4

03 7-Apr 9:00 AM C 6

03 7-Apr 9:00 AM D 1

03 7-Apr 12:00 PM A 0

03 7-Apr 12:00 PM B 8

03 7-Apr 12:00 PM C 4

03 7-Apr 12:00 PM D 0

03 7-Apr 3:00 PM A 0

03 7-Apr 3:00 PM B 3

03 7-Apr 3:00 PM C 2

03 7-Apr 3:00 PM D 0

03 7-Apr 6:00 PM A 0

03 7-Apr 6:00 PM B 5

03 7-Apr 6:00 PM C 6

03 7-Apr 6:00 PM D 0

03 7-Apr 9:00 PM A 0

03 7-Apr 9:00 PM B 7

03 7-Apr 9:00 PM C 5

03 7-Apr 9:00 PM D 0

04 7-Apr 9:00 AM A 0

04 7-Apr 9:00 AM B 7

04 7-Apr 9:00 AM C 5

04 7-Apr 9:00 AM D 0

04 7-Apr 12:00 PM A 0

04 7-Apr 12:00 PM B 7

04 7-Apr 12:00 PM C 7

04 7-Apr 12:00 PM D 0

04 7-Apr 3:00 PM A 0

04 7-Apr 3:00 PM B 8

04 7-Apr 3:00 PM C 7

04 7-Apr 3:00 PM D 1

04 7-Apr 6:00 PM A 0

04 7-Apr 6:00 PM B 6

04 7-Apr 6:00 PM C 7

04 7-Apr 6:00 PM D 2

04 7-Apr 9:00 PM A 0

04 7-Apr 9:00 PM B 7

04 7-Apr 9:00 PM C 6

04 7-Apr 9:00 PM D 1

05 7-Apr 9:00 AM A 8

05 7-Apr 9:00 AM B 8

05 7-Apr 9:00 AM C 8

05 7-Apr 9:00 AM D 4

05 7-Apr 12:00 PM A 8

05 7-Apr 12:00 PM B 7

05 7-Apr 12:00 PM C 8

05 7-Apr 12:00 PM D 5

05 7-Apr 3:00 PM A 8

05 7-Apr 3:00 PM B 7

05 7-Apr 3:00 PM C 8

05 7-Apr 3:00 PM D 5

05 7-Apr 6:00 PM A 8

05 7-Apr 6:00 PM B 7

05 7-Apr 6:00 PM C 8

05 7-Apr 6:00 PM D 2

05 7-Apr 9:00 PM A 8

05 7-Apr 9:00 PM B 8

05 7-Apr 9:00 PM C 8

05 7-Apr 9:00 PM D 8

06 7-Apr 9:00 AM A 0

06 7-Apr 9:00 AM B 10

06 7-Apr 9:00 AM C 7

06 7-Apr 9:00 AM D 3

06 7-Apr 12:00 PM A 0

06 7-Apr 12:00 PM B 8

06 7-Apr 12:00 PM C 8

06 7-Apr 12:00 PM D 0

06 7-Apr 3:00 PM A 0

06 7-Apr 3:00 PM B 8

06 7-Apr 3:00 PM C 8

06 7-Apr 3:00 PM D 0

06 7-Apr 6:00 PM A 0

06 7-Apr 6:00 PM B 8

06 7-Apr 6:00 PM C 7

06 7-Apr 6:00 PM D 3

06 7-Apr 9:00 PM A 0

06 7-Apr 9:00 PM B 8

06 7-Apr 9:00 PM C 8

06 7-Apr 9:00 PM D 5

07 7-Apr 9:00 AM A 0

07 7-Apr 9:00 AM B 7

07 7-Apr 9:00 AM C 7

07 7-Apr 9:00 AM D 2

07 7-Apr 12:00 PM A 0

07 7-Apr 12:00 PM B 8

07 7-Apr 12:00 PM C 3

07 7-Apr 12:00 PM D 7

07 7-Apr 3:00 PM A 0

07 7-Apr 3:00 PM B 6

07 7-Apr 3:00 PM C 5

07 7-Apr 3:00 PM D 4

07 7-Apr 6:00 PM A 0

07 7-Apr 6:00 PM B 8

07 7-Apr 6:00 PM C 2

07 7-Apr 6:00 PM D 4

07 7-Apr 9:00 PM A 0

07 7-Apr 9:00 PM B 7

07 7-Apr 9:00 PM C 4

07 7-Apr 9:00 PM D 2

08 7-Apr 9:00 AM A 0

08 7-Apr 9:00 AM B 4

08 7-Apr 9:00 AM C 0

08 7-Apr 9:00 AM D 0

08 7-Apr 12:00 PM A 0

08 7-Apr 12:00 PM B 2

08 7-Apr 12:00 PM C 0

08 7-Apr 12:00 PM D 0

08 7-Apr 3:00 PM A 0

08 7-Apr 3:00 PM B 8

08 7-Apr 3:00 PM C 0

08 7-Apr 3:00 PM D 0

08 7-Apr 6:00 PM A 0

08 7-Apr 6:00 PM B 7

08 7-Apr 6:00 PM C 0

08 7-Apr 6:00 PM D 0

08 7-Apr 9:00 PM A 0

08 7-Apr 9:00 PM B 4

08 7-Apr 9:00 PM C 1

08 7-Apr 9:00 PM D 0

09 7-Apr 9:00 AM A 0

09 7-Apr 9:00 AM B 3

09 7-Apr 9:00 AM C 7

09 7-Apr 9:00 AM D 0

09 7-Apr 12:00 PM A 0

09 7-Apr 12:00 PM B 7

09 7-Apr 12:00 PM C 7

09 7-Apr 12:00 PM D 0

09 7-Apr 3:00 PM A 0

09 7-Apr 3:00 PM B 7

09 7-Apr 3:00 PM C 5

09 7-Apr 3:00 PM D 0

09 7-Apr 6:00 PM A 0

09 7-Apr 6:00 PM B 4

09 7-Apr 6:00 PM C 3

09 7-Apr 6:00 PM D 0

09 7-Apr 9:00 PM A 0

09 7-Apr 9:00 PM B 5

09 7-Apr 9:00 PM C 5

09 7-Apr 9:00 PM D 0

10 7-Apr 9:00 AM A 0

10 7-Apr 9:00 AM B 6

10 7-Apr 9:00 AM C 2

10 7-Apr 9:00 AM D 1

10 7-Apr 12:00 PM A 0

10 7-Apr 12:00 PM B 8

10 7-Apr 12:00 PM C 3

10 7-Apr 12:00 PM D 0

10 7-Apr 3:00 PM A 0

10 7-Apr 3:00 PM B 6

10 7-Apr 3:00 PM C 3

10 7-Apr 3:00 PM D 0

10 7-Apr 6:00 PM A 0

10 7-Apr 6:00 PM B 1

10 7-Apr 6:00 PM C 0

10 7-Apr 6:00 PM D 0

10 7-Apr 9:00 PM A 0

10 7-Apr 9:00 PM B 2

10 7-Apr 9:00 PM C 1

10 7-Apr 9:00 PM D 0

11 7-Apr 9:00 AM A 0

11 7-Apr 9:00 AM B 2

11 7-Apr 9:00 AM C 2

11 7-Apr 9:00 AM D 0

11 7-Apr 12:00 PM A 0

11 7-Apr 12:00 PM B 2

11 7-Apr 12:00 PM C 0

11 7-Apr 12:00 PM D 0

11 7-Apr 3:00 PM A 0

11 7-Apr 3:00 PM B 0

11 7-Apr 3:00 PM C 0

11 7-Apr 3:00 PM D 0

11 7-Apr 6:00 PM A 0

11 7-Apr 6:00 PM B 0

11 7-Apr 6:00 PM C 1

11 7-Apr 6:00 PM D 0

11 7-Apr 9:00 PM A 0

11 7-Apr 9:00 PM B 0

11 7-Apr 9:00 PM C 0

11 7-Apr 9:00 PM D 0

01 8-Apr 9:00 AM A 0

01 8-Apr 9:00 AM B 7

01 8-Apr 9:00 AM C 3

01 8-Apr 9:00 AM D 1

01 8-Apr 12:00 PM A 0

01 8-Apr 12:00 PM B 8

01 8-Apr 12:00 PM C 4

01 8-Apr 12:00 PM D 0

Re: Database Dev't

DanPatterson_Retired — Tue, 05 Jan 2016 22:17:23 GMT

the GeoNet Lounge is not the place to post these types of questions A more suitable place to move this post can be found in the GeoNet Place structure.

GeoNet Community Structure

You might want to consider Managing Data or somewhere similar or it may remain languishing and ignored.

PS a few lines of your data structure will suffice

Re: Database Dev't

ChadKopplin — Tue, 05 Jan 2016 22:25:38 GMT

try removing the space between time and AM/PM ArcMap would look at these as two separate attributes because of the space.

Re: Database Dev't

DanielWaktola — Tue, 05 Jan 2016 23:21:58 GMT

Thanks.

Re: Database Dev't

DanielWaktola — Tue, 05 Jan 2016 23:22:20 GMT

Thanks

Re: Database Dev't

ChrisSmith7 — Wed, 06 Jan 2016 06:35:53 GMT

You could also store date and time together, unless you need it split-up - e.g. something like "1900-01-01 00:00:00"

Word of caution, Excel isn't really a database, it's a spreadsheet. If you want to really make a database, I would skip over Access and go right to SQL Server - you can get the "express" version for free:

SQL Server Express Edition | Microsoft

Here's a basic tutorial:

Tutorial: SQL Server Management Studio

In SQL, you could store this as datetime (or datetime2) or date only as date and time only as time (split-up).

topic Re: Database Dev't in Data Management Questions

Database Dev't

Re: Database Dev't

Re: Database Dev't

Re: Database Dev't

Re: Database Dev't

Re: Database Dev't