Creating 'sub-records'?

[Mgrac1]

I am currently working on a project that contains data on criminals. The tables I am working with list each defendant once for every crime or violation they commit. For example the table looks something like this:

Doe, John...DWI
Doe, John...grand theft auto
Doe, John...petit larceny

Because this evaluation is centered around jail populations, I need to list the individual only once followed by his original offense in the subsequent field and their additional charges below them, in a fashion similar to this:

Doe, John...DWI
->...grand theft auto
->...petit larceny
This way I can still keep track of true populations and their offenses without running into the problem of double-counting an individual. I am working with THOUSANDS of different individuals on a daily basis so simply going through a table and deleting it manually would take days to accomplish and be counterproductive. I have been working on solving this problem for the better par t of the week and now have decided to break down and ask the help of an expert, or at least someone with more knowledge than I. I hope this question was presented clearly enough with my examples that I gave, unfortunately the information is not for public consumption at this point so those examples will have to suffice for the time being.



Note:
while I will be running an analysis in a table, perhaps I could use a cascading/combo box in a form view to achieve what I want?

 

[stopher]

I would expect to see the tables to be designed something like:

Offenders:
OffenderID (PK)
FirstName
Surname

Crimes:
CrimeID (PK)
CrimeDescription

CrimesCommitted:
CrimeCommitted
OffenderID
CrimeID
DateCommitted

The third table here is similar to your but I've just used ID's instead of descriptions.

From the above you can get a list of offenders (from the offenders table). You can also list crimes by offenders.

I guess it's possible to make an entry in the offenders list without them having committed a crime. I which case you can do an aggregated inner-join query to list offenders that have committed a crime.

As for listing the offender only once with their list of crimes, this is normally done in a report using Grouping. Create a group level for Offenders and add a header then add the Offender field to the Group Header. The crimes then go in the detail.

hth
Chris

 

[Pat Hartman]

Once your tables are properly structured, you can create forms with subforms and reports with subreports to manage and display the data.

PS - take off your spreadsheet hat. Your idea of deleting the offender's name would be disasterous in a relational database. there would be no way to sort the records since all the records with blank criminal names would sort to the top. There would be no way to keep them in their place. Tables are unorderd sets of data and the only way to ensure that records are returned in a particular order is to sort them and by removing the criminal name, you eliminate the field on which you need to sort.

 

[redneckgeek]

... I need to list the individual only once followed by his original offense in the subsequent field and their additional charges below them, in a fashion similar to this:

Doe, John...DWI
->...grand theft auto
->...petit larceny

So how do you know which is their "original offense"?

 

[Pat Hartman]

In a spreadsheet, the original offense would be the first one in the list. In a relational database, you cannot rely on position since position in a list is not guarenteed unless you sort the list. you will need to use an autonumber primary key so that you can sort by the autonumber to return offenses in the order in which they were entered into the table.

 

[redneckgeek]

In a spreadsheet, the original offense would be the first one in the list. In a relational database, you cannot rely on position since position in a list is not guarenteed unless you sort the list. you will need to use an autonumber primary key so that you can sort by the autonumber to return offenses in the order in which they were entered into the table.

'twas a trick question

 

[Mgrac1]

Thanks for the responses, Ill be muddling through this project as data comes available. Honestly, the order in which offenses are displayed are more or less moot for the time being. The date of the offense is irrelevant to the goal of the evaluation. However it may be wise to sort it all now using a pk# so the data can be utilized in future evaluations.