Model Railroading Waybills Database -- Help solving a few things, and also making it look polished

[taholmes160]

Good Evening Everyone:



I'm a model railroader, and I am modeling a small shortline in southern Colorado. As I am sure you can appreciate, there are many flavors of model railroaders, some like to build beautiful trains that run through spectacular scenery, but dont DO much, others of us put the focus on moving "Freight" Im one of those. Part of moving "Freight" is keeping track of what each car is carrying, where it came from and where its going. This is commonly done with paper waybills and car cards -- each car card identifies a specific car, and the waybill explains where and how it is going (see this link for additional info if you are interested)

There are a number of different forms out there, and also various pieces of software to help create the forms and paperwork. I happen to enjoy developing databases, and also need to save money, so I chose to develop my own database to generate the paperwork etc.

I've attached a copy of my database written in access, and I am starting this thread as a place to post any questions that come up.

The first one is related to the query that drives the frmFreightWaybill (called QryWaybills). For some reason, when I run that query, it shows up each entry in TblWaybills twice. I suspect its related to TblRoads and the way that I am pulling that value, b/c it started when I added that part of the query, but I'm not sure how to solve it

The second item is that I want to begin the process of cleaning it up and making it look "professional" I have no interest in selling it or anything, but I do want it to look good, like someone who actually knows what he's doing developed it.

I would sure appreciate any help or comments that you can offer.

SLRG_Waybills_21_to_Send.zip

Thanks
Tim

[June7]

Conventional design is one form does data entry to one table. Why have form bound to a query that pulls in every table? The query pulls in tblWaybills twice. There is no JOIN between the two tblWaybills (which would be a self-join and not appropriate for this data anyway) and therefore results in a Cartesian relationship. Remove the second tblWaybills and join tblBusinesses to the one tblWaybills. Yes, tblBusinesses will be pulled in twice because this is lookup source for two fields in tblWaybills and then tblSates will also be in the query twice. And then tblRoads as well. This query may be appropriate for a report but don't think is for a form.

Also, probably should not be INNER JOIN for each relationship.

SELECT TblWaybills.WaybillID_PK, TblWaybills.Notes, TblWaybills.Print, TblWaybills.[Enter/Exit], TblCommodities.Commodity, TblBusinesses.CompName, TblBusinesses.CompCity, TblSates.StateAbr, TblCarCodes.AARCode, TblCarCodes.CarType, TblBusinesses_1.CompName, TblBusinesses_1.CompCity, TblSates_1.StateAbr, TblSates_1.StateName, TblRoads.Road, TblWaybills.Print
FROM TblRoads AS TblRoads_1 RIGHT JOIN ((TblBusinesses AS TblBusinesses_1 LEFT JOIN TblSates AS TblSates_1 ON TblBusinesses_1.StateID_FK = TblSates_1.StateID_PK) RIGHT JOIN (((TblSates RIGHT JOIN (TblBusinesses RIGHT JOIN (TblWaybills LEFT JOIN TblCommodities ON TblWaybills.CommodityID_FK = TblCommodities.CommodityID_PK) ON TblBusinesses.CompanyID_PK = TblWaybills.[Shipper ID_FK]) ON TblSates.StateID_PK = TblBusinesses.StateID_FK) LEFT JOIN TblCarCodes ON TblWaybills.AARTypeID_FK = TblCarCodes.AARTypeID_PK) LEFT JOIN TblRoads ON TblBusinesses.CompRoadID_FK = TblRoads.RoadID_PK) ON TblBusinesses_1.CompanyID_PK = TblWaybills.[Consignee ID_FK]) ON TblRoads_1.RoadID_PK = TblBusinesses_1.CompRoadID_FK
WHERE (((TblWaybills.Print)=Yes));

Might want to rename tblSates to tblStates. Kudos for naming convention not using space nor punctuation/special characters (underscore acceptable exception). Also for avoiding reserved words as names, such as Name or Date. I would expect to see some date type fields, at least in tblWaybills.

Each issue encountered in development should be a new thread. Old threads (answered or not) get less attention than new ones.

[taholmes160]

HI June:

I included the second tblWaybills because as soon as I try to query tblBusinesses the second time, the query stopped working

I Guess I am approaching incorrectly getting my data to fill in the combo boxes in FrmCreateWaybillsbyCommodity -- what would be the proper way to do that?

Thanks, I'll work on this

TIM

[taholmes160]

Hey June7:

I just started looking at things, and realized something, the query called QryWaybills is NOT for the input form, its used to power the report called RptFreightWaybill

Tim

[June7]

The query I posted works.

The Shipper and Consignee comboboxes RowSource could be a little simpler but should work fine. Might want them to Requery in the form Current event as well.

For Car Type, maybe hide the AARTypeID_PK - should users see the AARCode?

Might want to remove the 0 Default Value from number fields in table.

Consider changing Allow Zero Length property on text fields to No. That's my preference.

[taholmes160]

Cool -- Awesome -- thanks

TIM

[June7]

Wait a second, just now testing the Shipper and Consignee comboboxes and not working. Get back to you in a bit. Okay, had to find a commodity that had associated shipper/consignee. Seems to work. You might explore the NotInList event of combobox.

[taholmes160]

SWEET -- thank you so much -- I am curious, in the datasheet view of the QryWaybills is a box called Expr1002 -- im that good at reading SQL, but im not finding where it comes from, or what it does, and I am curious about it. Also, ive never seen left and right joins before, so I will need to do a bit of research on them -- it does look like its working, so thank you again

TIM

[June7]

It's because the Print field is pulled in twice. Access assigns the alias because cannot be multiple fields with exact same name. Remove the one that doesn't have filter criteria under it. Sorry didn't see that before.

[taholmes160]

No Problem at all -- I REALLY appreciate all the help -- I have a lot to learn -- that not in list event looks interesting, I'll do some research on it


TIM

[orange]

Tim,

I think you should create a data model for your "business" to ensure your design matches your requirement. Once you get the data model/blueprint for your tables and relationships tested, revised and vetted, you'll have a design to match your business facts. I'm not saying you can't jump into physical Access and create something. I am saying that doing so is a long way round and any design short comings could cause data to be inaccessible without considerable effort.

Here is a link to info re Database Planning and Design that may be useful. Specifically, the tutorials from RogersAccessLibrary and the Stump the Model may be relevant.

Good luck with your project.

[orange]

Here's a draft model for consideration.


You may also get insight from this free data model for Freight Forwarders from Barry Williams' site.

[taholmes160]

Cool -- Thanks Orange -- I really appreciate it -- I'll take a look at that data model

TIM

[orange]

I hope it's helpful.

Here's a note from my previous post:
Here is a link to info re Database Planning and Design that may be useful. Specifically, the tutorials from RogersAccessLibrary and the Stump the Model may be relevant.

Good luck.