Using specific fields on database as fieldlist/combobox options

[CrashTestDummy]

Hi there.


I am no access genius by any means but know XL very well. At present our company uses an XL template for every crash we are referred to and these are then saved with photos and data relevant to the crash in a separate folder. This makes it hard to obtain stats and stuff on crashes (time or location etc etc.

My thought was to use Access to save the jobs into making it saved all in one place and easier to obtain stats etc etc

(refer attachment)
I have made a main-table and main-form which records all the XL data that staff currently use. (not the best way but all i know how to do with myskillset). I made a couple of smaller tables for staff members, inspection agents, tow operators etc as these change from time to time and someone can update as required.

Everything is working (dropboxes and lists etc) but I want do one more ..... I want to take the make and models as entered then have the vehicle section display them for the person entering the data.
Everything i have tried via queries or lists returns only the field not the data in that field ..... Can it be done.
I also created a new table called vehiclesinvolved and my thought was just to use an sql theory to populate the 5 vehicles on a mouse click or button press etc.

any help appreciated.

Q

[ssanfu]

Welcome to the forum.

Based on the image you posted, here are some of my thoughts......

"Old Programmer's Rule" is this: If you can't do it on paper then you can't do it in Access. Until you can draw out your tables AND rules for how they change for each function you want to perform, you are going nowhere fast.


So based on that (and not knowing what your data looks like), here are some of the tables (to begin with) that I can see in the image:

tblCrash_Info_Data
--------------------------
CrashInfoID_PK (Autonumber)
CrashType_FK <------ link to CrashType Table
Imvestigator1_FK <------ link to Imvestigator Table
Imvestigator2_FK <------ link to Imvestigator Table
CrashDate
CrashTime
CrashRoad
CrashSurburb
CalloutNum
FatalNum
EventNumber
FileNumber
CrashComment
VehicleCount
PersonCount
FataltyCount



tblCrash_Vehicles_Info
----------------------------------------
CrashVehicleID_PK (Autonumber)
CrashInfoID_FK <------ link to tblCrash_Info_Data
VehicleMake_FK <------ link to Vehicle Make/Model Table
VehicleModel_FK <------ link to Vehicle Make/Model Table
VehicleInspectCompany_FK <------ link to Vehicle Inspect Company Table
InspectionType_FK <------ link to Inspect Type Table
Mobility_FK <------ link to Mobility Table
Requestor_FK <------ link to Requestor Table
Releasor_FK <------ link to Releasor (??) Table
VehicleYear
Rego
Driver_Rider
ReleasedTo
ReleaseDate
SpecNotes




tblPerson_Inveloved_Details
------------------------------------------------
PersonInvelovedID_PK (Autonumber)
CrashInfoID_FK <------ link to tblCrash_Info_Data
Status_FK <------ link to Status Table
VictimSup_FK <------ link to VictimSup (??) Table
Position_FK <------ link to Position Table
Autopsy_FK <------ link to Autopsy Table
Vehicle_FK <------ link to Vehicle Table (maybe to CrashVehicleID_PK)
Exhibits_FK <------ link to Exhibits (??) Table
FirstName
MI
LastName
DOB
Address
City
Phone
DiedDate
PrimaryContact



You already have some tables created. I don't see anywhere on the form where the Tow Operators are recorded.




Some suggestions:
Use only letters and numbers (exception is the underscore) for object names.
Do not use spaces, punctuation or special characters in object names. (this means any object)
Do not begin an object name with a number.
Do not use Look up FIELDS, multi-Value fields or Calculated fields in tables.



Some reading
Microsoft Access Tables: Primary Key Tips and Techniques
Autonumbers--What they are NOT and What They Are

[Homegrownandy]

Anoter suggestion: change that black background

[orange]

In addition to the comments received so far, you might find this free, generic "accident reports" data model from Barry Williams' site to offer some assistance. The intent is to show generally how tables may be related to one another based on "business facts".
For example :
an Individual may be at 1 or many Events
an Event may have 1 or many Documents

I will also suggest that familiarity with Excel may not be applicable to Access. You may have to replace some spreadsheet (wide and short) concepts with tables and relationships, normalization, shared data....
Good luck with your project.

[CrashTestDummy]

Thanks for the replies .

@ssanfu
I only have the 1 database for ALL the crash data (location, vehicle and persons). I think my table has like 240 fields, 1 for each box you see in the attachment on my first post.
I used the wizard and imported from XL and played around to get to that stage.
I know that by doing it this way I have limited myself to the number of cars that I can record in a crash or the number of people involved, say a bus runs a light and hits 5-6 other vehicles I wouldn't get all that data or those people.
I just didn't know how to link 3-4 cars from a separate table to this crash and then the same with the people, how to link those people to the same crash.


@Homegrownandy
had to start somewhere and that will become grey later on, just playing for now.


@orange
I will look at that site, at this stage I can see this being to big a project for my skill set with access.

[orange]

CTD,

I only have the 1 database for ALL the crash data (location, vehicle and persons). I think my table has like 240 fields, 1 for each box you see in the attachment on my first post.

If you want to create a database, then you'll have to use and follow some database concepts.
You will learn more about database design by working through 1 or 2 of the tutorials from RogersAccessLibrary mentioned in the Database Planning and Design link in my signature. Normally takes about 30-45 minutes per tutorial - and you will learn.
I recommend you work through the tutorial(s) and experience the process involved. Get an understanding of the concepts and the experience of designing a database, THEN do the same with your own project.

Good luck.

[ssanfu]

@ssanfu
I only have the 1 database for ALL the crash data (location, vehicle and persons). I think my table has like 240 fields, 1 for each box you see in the attachment on my first post.

Actually, you have 1 database (the container that holds the tables, queries, forms, reports, modules) and 1 TABLE (with 240 fields) that has all the data.

I used the wizard and imported from XL and played around to get to that stage.

So your table is designed like an Excel spreadsheet. This is a common design when converting from Excel (spreadsheets)l to Access (databases).
It is so common that it actually has a name: "Committing Spreadsheet".



Any chance you would post your dB?
Make a copy of your dB, delete all records except 1 or 2 crashes, change any sensitive data, C&R, then compress (zip).