Confirmation on what I am doing

[newtoAccess]

Please confirm what I have been learning:


Use as many tables a possible:
Every table should have a keyed Field usually xxx_Id
Create links between tables: Usually one to many using the keyed field on the single use table to a many use table. This allows for less duplication of data. This saves space.
Create a query that shows all data.
Create a “Data Entry Form” based on the “Show all data query”.

Use the form to add/edit/find data from all the tables.

My problems seem to be on the form. I have a hard time adding to the tables and also using a drop down to find data to edit it. I am not sure what type of field to use? Where to get the data for the field such as, is it from the table or the query. Many times I get an “error” msg in the field or I see a name and select it and nothing changes.

Attached is a play db with linked tables.
My form is divided into two. The bottom displays the fields of the tables that are found during the search in the top half <a test area>. While the top half is what I am playing with. I am trying to set it up to allow additions to each of the tables and find what I am looking for. I am trying to use a edit/add list drop down where I can select a persons name if the person is not on the list I would like to be able to add it for future use.

In other db’s I used a query to join the first and last names then update the table’s Full Name field.
Is there a better way of joining the first and last names?

[ajetrumpet]

hello again sir. I would assume that this attachment is the product of all that you've learned from the guru's here. Right?

There are a few fundamental things wrong with what you have, and it would be extremely beneficial for you to know them so you can build a product that looks good. Not trying to be a preacher, but I can help you out enough to straiten the track a little bit.

First of all, there is no hierarchy established with your data. Databases are pretty much all the same, with some exceptions, but most people can follow the same pattern for almost everything and produce a satisfactory result. Every db, if it's structured even close to correct, follows a hierarchy of somekind. The premise is the one-to-many relationship. Your project looks like you're keeping track of people and their work schedules. Not sure where that is going, but from the data you have now, a correct setup would look like this:

PEOPLE

• workername
• province
• address
• phone
• postcode
• city
• state
• notes

WORKHISTORY

• hours
• day
• month
• year
• payrate
• anything else

That's all that's needed really, just 2 tables, but other tables can be used, such as PAYCHECKS, to record periodic paycheck data.

Second, when you say "use as many tables as possible", that is not even close to correct. Whoever said that is completely wrong, especially if it said to a beginner, because beginners of any discipline have a tendency to take advice for what the words say, and that's it. As you can see in the above example I gave you, the number of tables I would need to do this same thing is half the number you have now.

The next thing you really need to change is your join types between the tables. Based on the number of posts I've seen from you this week, I would guess that join types are not that familiar to you, but I could be wrong. I won't mention the right joins and such that you have now, because they are not needed, and if you (heaven for bid) keep the structure that you have, they should be changed to inner joins anyway.

As far as using keys in every table, YES, in general every table that keeps data that is eventually going to be displayed to a user should have an autonumber field in it that identifies it. This is completely normal, and I personally do it in every table I ever create. The only exception to me personally, are lookup tables. An example of a lookup table would be discount rates for a retail store product. Whenever they are needed, they are looked up via code or queried. They never appear in records that come directly from a table.

With all of that said, the one thing I believe that will benefit you greatly is the hierarchy. If you know that, a lot of other things simply fall into place if you are familiar with the tools to make the hierarchial data effecient (like joins, relationships, keys, etc...). To give you a better understanding of the hierarchy, programming languages that use objects do the same thing. It's the same format. So do corporations.

• many workers to a team leader
• many team leaders to a departmental manager
• many departmental managers to a building
• many buildings to a regional manager
• many regional managers to a vice president
• many vice presidents to a CEO

and the examples go on and on and on. You can produce a structure that is, more than 99% of time, just fine, if you first identify the chain of command between your "objects", or "drivers". In your current database, the highest link of the chain of command is the worker. Thus, workers should be assigned to one table. There are many "pay periods/work periods" per worker. Thus, this is the next link in the chain, further down than the worker. And there you have a parent/child relationship. Every link is like this, and it goes on and on, until there are no more children "drivers" to deal with.

If that makes sense, than you've just gotten smarter. Not by me of course, because I know nothing really, but IMO, hierarchies are everything. Without them, a db is impossible and a waste of time really.


This is off subject, but to give you a great example of this again, I just finished compiling my own vba system to get help with it faster, and the whole programming language is based on the same concept. For example, most of the vba that you see has this hierarchy:

LIBRARY FILE
          OBJECT CLASS
                    METHOD
                              PARAMETER
                                        DATA TYPE / ENUMERATION (for parameter)
                    PROPERTY
                                        RETURN TYPE (for property)

See...Microsoft even started it!

[newtoAccess]

The attached db had no use. I was lost for something to create. (I am always lost, as you can see by my spelling and questions). I was trying to learn how to use several tables, get them into a form where I can use the form to update more than one table at a time and retrieve information from the tables using only one form.
As for where I gather my access info it is from several places. Mainly my interpretation.

With that said you do not recommend having a table just for State/Province? or any table just for one field?

Is there a way on a form to be able to append a table and on the same form be able to select data from the same table?
eg: If a user does not see the data they are looking for in a table they can add it. Next time they see what they are looking for they can select it.

I usually have one form for adding to a table and one form for extracting/finding data. Then a button to print what was found if needed.


In real life:
I am doing volunteer work creating two db's as a learning experience.

1st one. There is a spring convention for approx 300 members, requiring name cards, control if the name cards had been printed, or not required, hotel lists, Schedule for the various meetings, seating cards, and whatever may come up that may benefit from the data in the db. I am trying to use easy to use forms as the users are all seniors with less PC experience then me. (that's bad) <Hard to be leave anyone has less experience than me.>

The second one (db) is a survey sent to several high schools with 28 questions. Again for a volunteer organisation. There is a overview report by percent, followed by break downs by age, grade, gender, School for each question by count and percent. The only thing waiting on this is the layout of the reports. The data has been entered. I am having graphing problems with this one?

[ajetrumpet]

With that said you do not recommend having a table just for State/Province? or any table just for one field?

did you read what I wrote or not?

if you did, you can answer that question yourself. You don't need a province table because that information pertains to workers' demographics, right? just roll it on into the worker table. DONE.

Is there a way on a form to be able to append a table and on the same form be able to select data from the same table?
eg: If a user does not see the data they are looking for in a table they can add it. Next time they see what they are looking for they can select it.

''append a table'' doesn't make sense at all. it's hard to read and understand.

you can answer these questions of yours if you simply understand what a form is. I'm curious to know. Could you tell me what your view of a form is? What do you believe is its purpose? and how is it different from a table?

I only ask because if you can answer that question, you should be able to answer the rest of the questions you asked.

[newtoAccess]

A form can be used as an easy way to enter data into a table and a easy way to find data from a query or table or joined tables. correct?
You are saying it can't do both at the same time?

[ajetrumpet]

yep! Just wanted to know what you thought.

forms are generally used for scrolling through records, editing them and adding more records to tables. so yeah, you can any of these with a form.