Query/Report Question

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table setup that contains fields crew1 crew2 crew3 crew4

What I would like to do is make a query/report that will look at each field, and add up each occurance of an employee ID #

so if I have employeeid 1 in crew1 in one instance, and then in crew2 on another record. I would like my report to come back saying that employeeid 1 appears 2 times total.

OR i can consolidate the fields into one field called crew enter the ID # seperated by a comma..... have a query or something look at how many times id 1 appears, while ignoring all other entries in the field.

In the end I need a report that will pull out the EmployeeID from whichever field and tell me how many times it occurs there, report will specify each employee listed between a date range - i can handle the date range part.

Sorry if I make this sound confusing.
thanks
 
Scott

If you had a spreadsheet, crew1, crew2, ... would be the way you'd design a
database. With Access, you need to look into normalization to get the full
benefits of this tool. The table structure you've described will force you
(and Access) to work MUCH harder than you need to.

Here's an alternate design:

tblUnknown
UnknownID
ProjectDate (don't use "Date" as a field name -- it's an Access
reserved word)
EmployeeID (only one EmployeeID per row)

Without more description of what you're documenting, this is a very stripped
down table.

If you wanted to see how many times EmployeeID = 6 showed up between two
dates, a simple query of this table will get that.
 
Jeff, THank you for responding. Here is what I am doing - I work for an ambulance company. We need to input all of our call data such as - CallDate RunNo Location etc etc with all of the Call Times listed and The Crew that went on the Call...

And I am trying to get this, so I dont have to enter the data more than once for a single call.

We had this all in paradox at one point, but not anymore.....So I know it can be done in access as well
 
Back
Top