Data entry to populate multiple tables logic and process

Hi All -

I'm using Access 2016. I'm "relatively" familiar with Access and SQL (pardon the pun - or not). I do understand the fundamental logic and structure of relational databases. What I am struggling with are the mechanics.

I do know what I want to accomplish task-wise as it a daily process currently using an Excel spreadsheet.

Our company makes and sells a general construction product. We have a proprietary tool(s) that we send to the contractor at the jobsite (who may not be our direct customer) that helps them install our product. When a job is sold, I know who our customer is and have a projected "Shipping Date" for the sold product (not the tool) but I do not know who the jobsite contact is or when the true tblJob:FirstSheetDelivery is - and sometimes don't for months - not until just before we ship the product we have sold at which point I call our customer to identify the jobsite contact in order to acquire the appropriate address to ship the proprietary tool (via UPS, Fedex, etc).

We supply the jobsite contact with an RMA to return the tool when they are done with it.

I need to track the tool by date and contact(s).

Typical workflow is the following:

1. Estimating dept. notifies us of new job, with Job Number, Jobsite Info, Customer contact info, Company Salesperson info, Product Type, SQS (numeric quantity of product), and approx. date of first shipment of our product (which I need to know to make sure the tool arrives before the product). I record all of this data into spreadsheet.

2. Time passes................Job goes into production. Date of first shipment is now a solid known which is my trigger for contacting our customer to identify Jobsite Contact and obtain contact info for Jobsite Contact. I enter this new data associated with Job.

3. One of 20 identical tools is boxed to ship to Jobsite Contact. Date of shipment, tool number, tracking number, and any notes are recorded.

4. Time passes............Jobsite Contact is contacted to return tool. Jobsite contact returns tool. Date of return shipment and any notes are recorded.

Data Entry Needs:

1) I initially need to enter the basic data for a given job (tblJob, tblJobContacts, tblJobProducts). tblJobContacts will initially include our customer contact info (which could be new but often is not) and the name of the salesperson.

We have many repeat Customers who use a variety of jobsite contacts - some of whom are also repeats - but not always. Single customers may have several jobs going at once around the country. Multiple jobs will only have 1 jobsite contact - but some of the jobsite contacts may work for very large national companies. Meaning we may have a single Customer with multiple jobs using the same national company for jobsite erection at multiple sites - each site having a unique jobsite contact.

We may also have multiple ProductTypes in a single Job. However, we rarely add new products - really. I would call it a static list - -

We may ship more than one tool to a given Jobsite Contact - but very, very rarely.

2) At a later point in time I will need to recall a Job and enter Jobsite Contact info - but not necessarily shipping data. That could happen at the same time or later.

3) at a later point in time, I will need to enter shipping details for shipping the tool and at a much later date enter return shipping information.

I've attached my basic db.

I am struggling to understand: the logic of entering data into all the tables/fields required given my above-stated process. I want to make this super user friendly so that anyone here can use it - so I am thinking about forms, sub-forms, continuous forms, tabbed forms, etc. (none of which I am close to 100% with) for data entry - at this point - specifically for data entry.

Of course it would also be good to know how to create a form that asks what job I would like to review, type a job number, and have all of the relevant info appear like magic. But that can wait for a new post.

Right now, I'm primarily interested in any thoughts about how to best approach entering data and if my current db/table design will accommodate my needs.

Your guidance and thoughts - - and "how-to" are greatly appreciated.

Thank You!

Tim

First, I think you need to fix your structure--you've over normalized. Tables with only 1 real field of data (autonumber primary keys aren't real data) do not need to exist. Instead you store the actual value that is related into the table itslef. For example, tblShipping should simply store the 'UPS' or 'FedEx' value instead of the id to tblFrieghtType. You have 5 tables that can go.

Also on the structure side--no Job is directly related to a Company. It's indirectly related via contacts. Maybe this is correct, but as someone who only generally understands your data it seems like an issue. In the same vein, no contact is being shipped to. Again, that may be correct, but it would seem you would want a name attached to a shipping. Your structure doesn't allow that.

As for forms, I base every table that directly interacts with data (add/edit/delete) off of tables. When I have a 1-many relationship between tables the many table becomes a subform on the 1 table. Based on the database you posted, I would have a form for tblJob the top of which I would have the inputs for its data (JobNumber, JobName, Street, etc.). Then below I would have 3 tabs on that form--one for each subform I would use which would be based on tblShipping, tblJobProducts, tblJobContacts.

I understand the majority of what you explained. Thank you.

How would you restructure to link a contact to shipping? I made the assumption that "all things" would be connected and accessible via JobID. I am obviously incorrect in that assumption. I think this is where my confusion comes from.

All things are connected, but not directly in your model. Your structure allows multiple contacts to a single Job, you will not know specifically which contact a shipping is to/from. I don't know if that is an issue, it was a question I raised.

To have a shipping specifically tied to a contact you would put a foreign key to the contact table in your shipping table.

Also, because a job can have multiple contacts and its via the contacts that you assign a company, 1 job can have multiple companies attached to it. Again, don't know if that's an issue, but with your structure its a possibility.

This is why forms are last in the process. You first need to work out your tables then you move to reports. Reports make sure you can get the data out in the manner you need from your tables. If you can't get the data out like you need, no point wasting time working on a way to get data into your unuseable tables.

Yes - a single job will have the following contacts: Salesperson, Customer, and Jobsite Contact. I do need to know which contact (JobsiteContact) I am shipping to/from and dates.

Also, 1 (one) job will have multiple companies associated with it: 1) Salesperson (Epic Metals - always - which means that I likely can eliminate much of this table in the same manner as the previous 5 you mentioned); 2) Customer (point of initial sale - typically a General Contractor or Architect) who hires 3) a subcontractor to install our product (JobsiteContact). I need to have contact info for all three of these related to the JobID but only the JobsiteContact will be associated with shipping. The Customer and the JobsiteContact usually but not always work for different companies. It is also possible for the Customer and the JobsiteContact to be identical - - highly unlikely but possible.

So - another question as I think my dim bulb is beginning to brighten maybe a little: Process-wise it sounds like I should develop queries before I develop forms - and it sounds like forms act more like an interface to queries - - but how do I make the leap from that to simply entering data? What is the typical process?

For instance, I have many outstanding Jobs right now and I want to enter the data for each into the various tables - and have them related to each other - and build queries from that initial data.

Is there a "typical" process that experienced folks use?

I really apologize for having this mental roadblock. I am grateful for your patience.

Thank You!

Tim

I would consider these tables look up tables:-

tblShippingDirection
tblRole
tblProductType
tblFreightType

In that all they contain is a unique ID and a description. Basically these Table layouts are the same, therefore it would be possible to put the information from the four Tables into one table. Add an extra field to identify the group, the original table where the information came from. With this arrangement you just need one form to edit any of the groups within this look up table, a form I usually call frmlookUp and table named tblLookUp.

Uncle Gizmo said:

I would consider these tables look up tables:-

tblShippingDirection
tblRole
tblProductType
tblFreightType

In that all they contain is a unique ID and a description. Basically these Table layouts are the same, therefore it would be possible to put the information from the four Tables into one table. Add an extra field to identify the group, the original table where the information came from. With this arrangement you just need one form to edit any of the groups within this look up table, a form I usually call frmlookUp and table named tblLookUp.

Click to expand...

This sounds like an extremely elegant idea - - that unfortunately my thick mind doesn't know how to implement. Is the attached jpg what you are suggesting? How specifically would I utilize this?

No - just 3 columns
ID - autonumber PK field
Group - Text
Description - Text

See attached

ridders said:

No - just 3 columns
ID - autonumber PK field
Group - Text
Description - Text

See attached

Click to expand...

aha.....Now I am wondering how to utilize it for data entry. I am assuming a combo box but how do I tell the combo box to filter on "Group" and return a dropdown menu for all items of a certain group?

Process-wise it sounds like I should develop queries before I develop forms - and it sounds like forms act more like an interface to queries - - but how do I make the leap from that to simply entering data? What is the typical process?

Click to expand...

No. Forms should be interfaces for tables. Queries should support the output of data (e.g. Reports).

When I have an existing system (a bunch of spreadsheets) to convert to Access, I first set up my tables. I make sure they accomodate all the data I have and will have and make sure their relationships are correct. Next, from my spreadsheet I find the simplest set of data to test one instance of whatever it is I am modeling (for you that is a Job). I plug in all the data from the spreadsheet into my tables as appropriate. Usually at this point I find a field I missed or realize some fields are in the wrong table, or I need to establish a new 1-many relationship. I fix my tables to accomodate and then make sure that 1 sample instance now fits into my tables. Then I find a more complex instance and do the same thing to make sure that even though its more complex, my tables still work for it. Once I do that, I find the most complex instance I can and manually add it to my tables, making sure it all works.

Now, with 3 test instances in my tables I build the reports I need. With an existing system this is sort of easy--I already know what the reports are to contain and look like. I simply build queries to tie the data together until I have the ones I need to feed a report. Sometimes my tables don't lend themselves to the reports I need, I fix my tables, reinput my data and then continue on with my reports until everything is correct. Once I have my reports I then move onto forms and making a system for people to interact with the data (add/edit/delete/search).

I'm going to give you a different point of view.

Its fair to say plog & I don't completely agree about forms & I doubt either of us will ever convince the other. We both do what works for us

Where we do agree fully is in the need to test table design thoroughly using real life or realistic example data before moving on to the next stage.
I always normalise as far as is possible but will occasionally make exceptions where this helps functionality

Once that process is done, I then move onto designing forms and queries.
I normally do reports last.

I use tables as the data source for many forms.
However, where appropriate I will also use queries based on 2 or more tables as the record source for my forms / subforms
In extreme cases, my queries may be based on 6 or more tables though this is rare.

However, it is important to realise that queries can become read only in certain situations. See this link for information on reasons for this.
http://allenbrowne.com/ser-61.html

In such cases, the query would be fine for reports but unsuitable for most forms unless of course you want them to be read only!

Every application has a requirement for some number of simple lookup tables. This data is generally used to populate combos or listboxes and to control RI. I decided a very long time ago (approximately 1984 using COBOL, CICS, IMS) that I would make a mini-app to solve this problem once and for all. Over the course of the next 20 years, I implemented the app using various technologies in dozens of mainframe applications. When I discovered Access in the 90's, I implemented it in Access and to this day, I include it in every single application I develop. There are two tables, two forms, and two reports. And very little has changed with the concept in 30 years so I think it probably works pretty well. This sample has a few other useful features in addition to the table maintenance functions.

I do use code in the BeforeUpdate event of each combo to ensure that the ActiveFlg is true. In order to show the stored values, everything has to be in the RowSource. However, over time, some items are no longer valid so they are marked as inactive and that means your code needs to prevent them from being selected for new items or possibly to change old items depending on the ChangeDT. Sometimes I sort the RowSource by the ActiveFlg and then the LongName so make the inactive items drop to the bottom of the list to keep them out of the way. The AuthCode field is used to control who can modify the table. Some tables are IT only because changes to those tables would require code changes to the app. Other tables can be updated by admins only, and some can be updated by anyone. You have to decide for each app how you want to do this.

Pat's updated dB ~TableMaintExample~ updated to 64 bit available HERE:-

https://www.access-programmers.co.uk/forums/showpost.php?p=1560001&postcount=17

You made more changes than that Uncle.
1. The database no longer opens to the about form
2. The database is now tabbed rather than overlapping windows.

Please take it down.

Exactly what code was causing the problem? It is most likely left over from some other project. It can almost certainly be removed since the table maintenance feature uses bound objects.

Pat Hartman said:

Please take it down.

Click to expand...

Why take it down? - it wouldn't work for me in 64-bit Access. The start-up form wouldn't load, it just froze. I copied everything into a new blank DB which is probably why it's not exactly the same as your original. I have no idea what your original was like because I can't use it! I don't understand your logic in asking for it to be taken down, but obviously I will if you insist, but then there will not be an option available for anyone with 64-bit...

Pat,

PMFBI but your sample database has exactly the same issue as a previous database you posted a month or so ago- if I remember correctly, that was the dates example??
The startup form frmAbout opens on a secondary monitor (if you have one).
If you don't have 2 monitors, you can't open the database at all.
That startup form does not any contain code that needs converting for 64-bit Access though other forms do

As before, the cure is to open that form in design view on the primary monitor, save & close the database.
Corrected version attached & I really haven't changed anything else
i.e. its not 64-bit compatible as it stands

Why take it down?

Click to expand...

Because you have violated the terms of usage. You have changed the application and redistributed it. That is specifically not allowed by the terms of the agreement. You may change it for your own use but you MAY NOT redistribute a modified version.



I removed the extra code so there should no longer be a problem with 64-bit.

Pat Hartman said:

Because you have violated the terms of usage. You have changed the application and redistributed it. That is specifically not allowed by the terms of the agreement. You may change it for your own use but you MAY NOT redistribute a modified version.

I removed the extra code so there should no longer be a problem with 64-bit.

Click to expand...

Hi Pat,
I've tested your updated sample dB in 64bit and it now loads correctly.

On loading, it now shows the terms and conditions you mentioned.

Seeing as the terms and conditions are important, it might be an idea to state them in the post, just in case another unexpected error creeps in. Someone else could find themselves in the embarrassing position of unknowingly breaching the terms and conditions.

I have removed the copy I fixed, as it doesn't load the terms and conditions information.

Merry Christmas!

Thanks Uncle, Merry Christmas