Creating a Database to Exclude Data?

[spyldbrat]

I was wondering if I can create a report that shows some of the information attached (I am hoping it attaches to this posting!)

In the end, I need a list of all employee's that have a date written on one of the white columns (Written/Final/Susp/Term), but the gray column is blank.

I would need the report to look like this

Emp Level Date Written
Emp 1 Final 10/9/15
Emp 2 Susp 10/15/15
Emp 3 Susp 10/6/15
Emp 4 Tern 10/5/15



In the event that there are two columns in a row that have something in the white column and nothing in the gray, than I would like it to return the oldest value. So, if there was a Term and Final date recorded in the white columns, I would like it to return the Final date. If all 3 columns under a category (Written, Final, etc) have a date than I don't want it to return a value at all.

I am not sure if there is some sort of database that can be set up and then a report made to show everything in a column?

[spyldbrat]

Here is the spreadsheet.

EEN

First Name

Last Name

# of Violations

Total Points

Written (Date Written)

Points @time of Written

Written (Date Counseled)

Final (Date Written)

Points @time of FINAL

Final (Date Counseled)

Susp(Date Written)

Points @time of SUSP

Susp (Date Counseled)

Term (Date Written)

Points @time of TERM

Term (Date Counseled)

18838

First Name 1

Last Name 1

4

13

n/a

n/a

n/a

10/08/15

13

10/09/15

18950

First Name 2

Last Name 2

3

25.5

10/14/15

11.5

10/15/15

n/a

n/a

n/a

10/15/15

14

19017

First Name 3

Last Name 3

1

14

n/a

n/a

n/a

n/a

n/a

n/a

10/06/15

14

19090

First Name 4

Last Name 4

1

15

n/a

n/a

n/a

n/a

n/a

n/a

n/a

n/a

n/a

10/05/15

15

[orange]

The quick answer is Yes. But the real answer is that Excel and Access are different, and are built on different object models.
Where as excel spreadsheets tend to be wide and short.
Access tables tend to be narrow and long, and represent attributes of a specific thing (entity).

I see things in your spreadsheet that suggest these things:

People
Violation
Occurrence and others

To understand the basics of database table design you could work through this tutorial.

Good luck.

[CJ_London]

When constructing your db, avoid using characters such as #, @, brackets and spaces - just use alphanumeric characters

[NTC]

building on O's comments; the way you post your wanting the report - that is the way that a db would store it inherently - if you were using a db. But your excel's side by side layout means you need to muck around with a union query to transform the layout.

not sure if you must use that excel going forward or whether that was just a dry run initial set up and now you want to build a db - if building a db then you'll have no problem getting what you seek.

[June7]

If data is stored as shown in the spreadsheet image, build a query with a calculated field to return date from the appropriate field. Another calculated field to determine what level the date is from. These could involve lengthy nested IIf() or VBA custom function.

If data structure is normalized as suggested, determining what date to show for each EEN would be more complicated because the parameters would be spread over several records.

IMO, it is a balancing act between normalization and ease of data entry/output. You will have to decide how far to go.

Saving a text value (n/a) in a column meant for date or numeric values will cause issues in Access. So whether you link to Excel or build table in Access, if you want a date/time value to be treated as a date/time by Access, then it must be in a Date/Time field. Same for numeric data - must be in a number type field.