Simple two-table DB 1

[tizhimself]

Each month an agent (8 total agents) provides a list with his name and a count of 14 police activities for the month:
Agent FName, LName
Activities:
Arrests 5
Cases Cleared 10
Vehicles Recovered 4
Value of Vehic Recov $8,000.00
.
.
Salvage Yard Inspection 6 (14th activity)
________________________________________________
I tried two tables, Agent and Activity but here's problem:
I need to be able to create a cross-tab query so that column 1 is the 14 activities for the month, column 2 is current year to date, column 3 is prior year summary, column 4 is summary of year prior to column 3, etc.
I took the 2-table approach but can't create the X-tab query as I want. In my Activity Table I made each activity a field (Agent Table 1 to many with Activity Table) & I think this is wrong. Seems like I need something like a purchase order DB with products being activities. Maybe an Activity Name Table, too?
Thanks for any help... I'm a volunteer doing work for the Virginia State Police.

 

[Trendsetter]

I would think that the 2 table approach is correct.

- in your second table you make no mention of a date field which would be the Activity date.

- you don't say why you can't get a crosstab query

- in effect you have a purchase order type situation which is the PO header information - Supplier/Address/date/delivery address etc... in table 1
item information - quantity/description/price etc.. in table 2 related by OrderID
table 1 for you is Agent
table 2 is the monthly activity details.
Column 2,3 & 4 of your query will need a great deal of thought to produce the results you are looking for.

Frank J Hill
FHS Services Ltd.
frank@fhsservices.co.uk

 

[tizhimself]

Frank:
Here's my present setup:
Agent Table:
AgentID
FName
LName
Code
Title

Activity Table:
Agent ID
Activity (Key Field)
DateActivity (Date when the agent data entered by the secretary, usually last day of the month))
Arrests
CasesCleared
.
.
SalvageYdEx

When I try to create a X-Tab Query I can only select the first three activities for the first column, whereas I want to see the entire list of 14. I based the X-Tab query on a query that returned all activities in the DB.
Once I get the X-Tax query working I'll work on the design to get the date columns set up correctly by interval.
DB available for checking...

 

[Trendsetter]

Hi,
I am not familiar with crosstab queries as I have never had to use them, I've always worked a different way, however it sounds to me as though there is a limit of three distinct sources allowed for the query.
I'm confident that by re-arranging your data sources you will be able to obtain the required result.

Frank J Hill
FHS Services Ltd.
frank@fhsservices.co.uk

 

[JeremyNYC]

Well, I think you should be able to get the crosstab query to work, though I don't have time to dive into it myself.

On the other hand, you could rearrange your table this way and make it easy as could be to build that query you need:

tblAgent
as you have it

tblActivityType
ActivityTypeID
ActivityTypeName

tblAgentActivity
AgentID
ActivityID
ActivityCount
DateSubmitted

That should make reporting quite easy.

On the other hand, someone around here may just bang out that crosstab query for you. Shouldn't be undoable.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Affordable Development, Professionally Done

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[tizhimself]

Jeremy:
I'll give your suggestion a go...Thanks