A little more help for a relative newbie, Please

[accessfleet]

I have a report using the following tables but, the report shows the ID number rather than the type or description text.

It is a little hard to follow and I would like to make the report more understandible.

Muchas grassious in advance..

John.


vehicle_typeIDtypedescription1Automobile
2light_duty class 1 or 2under 10,000 GVW3Super_duty class 3,4 or 510,001 to 19,500 GVW4Medium_duty class 619,501 to 26,000 GVW5Heavy_duty class 726,001 to 33,000 GVW6Heavy_duty class 8over 33,001 GVW7Construction_ Equipment
8Other


Veh_sub_typeIDVeh_sub_type1Fire_Equipped2Police_Equipped3Van4dump_3+yard5dump_5+yard6dump_8+yard7dump_10+yard8dump_ 13.5+yard9conveyor_dump6.5+yard10conveyor_dump7.8+yard11Trractor_Truck12Stake_truck13Sweeper14Water Tanker15Vac_Con_truck16Aerial_lift_truck17Roll_Back_truck18Trailer_1axle19Trailer_2axle20Trailer_3axle21Trailer_flowBoy22Trailer_box23Trailer_dump24Trailer_lowboy25Service_truck

EQ_sub_typeIDEq_sub_type1Wheel_Loader2Track_loader3Backhoe4Dozer5Wheel_excavator6Track_excavator7Grader8Roller9Gravel_crusher10Athey_loader11Screening_plant12Snowblower13Air_compressor14Tub_Grinder15Paver16Stone_Rake17Ditch_Witch_trencher18Skid_steer_loader19Asphalt_curb_machine20Sewer_pump21Sewer_cleaner22Blacktop_saw23mower_tractor24mower_lawn25Broom_pull26Welder27Chippers_brush28Forklift29Grinder30Generator31Chain_saw32Trash_pump33Vibratory_compactor34Road_widener35Drag_box36Maintainer37Concrete_mixer38Leaf_collector39Demolition_saw

 

[boblarson]

In the report's recordsource you use a query which is linked to the lookup table(s) and then you can put the description in. You link on the applicable PK/FK for that table.

 

[accessfleet]

Thanks Bob:

Report has right look now but the report no longer lists all the records with data in the Vehicle_type field.

SELECT vehicle_type.type, CIPplanyear.PlanYear, EQ_sub_type.Eq_sub_type, Veh_sub_type.Veh_sub_type, vehicle_type.description AS vehicle_type_description, units.[RADIO_#], units.[ACCT/DEPT], units.miles, units.hours, units.Description AS units_Description, units.Auto_Insurance_Schedule
FROM vehicle_type INNER JOIN (Veh_sub_type INNER JOIN (EQ_sub_type INNER JOIN (CIPplanyear INNER JOIN units ON CIPplanyear.ID = units.CIPplanyear) ON EQ_sub_type.ID = units.[equipment sub type]) ON Veh_sub_type.ID = units.[Vehicle sub type]) ON vehicle_type.ID = units.[Vehicle Type]
WHERE (((vehicle_type.type) Is Not Null));

It me again Bob this works but I dont understand why the list keeps getting shorter. I expected that the report order would change but still repaor the original 194 records
I'm still trying to figure out the best way, I don't want to be a nusance but any guidance would be appreciated mucho, mucho.

just units 194 records units,+veh_type 151 records and units+veh_type+veh_sub_type 111 records
SELECT units.[ACCT/DEPT], units.condition, units.[RADIO_#], units.[UNIT_#], units.miles, units.hours, units.CIPplanyear, units.[Vehicle Type], units.[Vehicle sub type], units.[equipment sub type]
FROM Veh_sub_type INNER JOIN (vehicle_type INNER JOIN units ON vehicle_type.ID = units.[Vehicle Type]) ON Veh_sub_type.ID = units.[Vehicle sub type]
WHERE (((units.condition) Is Not Null))
ORDER BY units.condition DESC , units.CIPplanyear;


Any Ideas what I did wrong, I have looked and looked but I just don't see the problem.

 

[boblarson]

If you can upload a copy of your database I (or someone else) might be able to take a look and see.

 

[accessfleet]

it is a little big

8mb front end, 21mb back end

Maybe too big to upload?

 

[boblarson]

it is a little big

8mb front end, 21mb back end

Maybe too big to upload?

Have you run COMPACT AND REPAIR on BOTH of them? Also, then if you do that and then ZIP the files, are they still over 2Mb? If so, I can PM you an email address so I can look at them. I won't have time until tonight but I could then. Let me know.

 

[accessfleet]

after compact & repair

win zip files are fe=982kb and be=9mb

 

[boblarson]

Okay - I sent you the files but got an error message. Apparently you can't receive either that big of file or you can't receive the type of file. So, anyway, I uploaded them to my website. I'll send you a link to them via a PM.

But for anyone watching this thread, here's what I had to say in the emails:

Here’s your backend back with changes to the Units table. I removed the lookup on the ACCT/DEPT field.

. There are a lot of things that could be done to make this better but I don’t have time to go into them. One BIG thing is that you should NOT use TEXT for your primary keys. You should use numbers (specifically autonumbers) and then you can have the other fields be text, including the Department ID’s if they are based on actual numbering. You should store the autonumber ID in the Units table instead of the Department ID normally. And then, if you look at the report’s record source you wll see how I put the extra table in and linked it so we could use the name. That brings me to another thing. Do NOT use NAME as the name of any object or field. That is an Access Reserved Word and is one of the worst ones you can use. That can honk up things later on down the line for you when you go to try to do certain things. So, I would rename it if I were you. Also, don’t use any special characters in field or object names. Those can cause problems as well. You can use an underscore ( _ ) if you must but try to avoid them too. Also don’t use spaces in the names and things will be simplified – you won’t have to always encapsulate things in square brackets if you don’t use spaces, special characters, or reserved words.

 

[accessfleet]

Bob:

Thanks for all the good recomendations. I'll try and adapt them in my future efforts. I'll be looking thing over today.

I really appreciate your interest and your guidance and help.

John..