New to Access - real basic help needed

[tc197]

Hey guys I'm new to access and was given a project at work to design a form to record and save information. My end goal is to have a form that can be filled out by a worker and have all of the information recorded in tables that can then be accessed later on and searched through. Sorry that these questions are going to sound horrible - I just started using access yesterday and have no luck searching the internet for solutions to get me further than this.

Problem 1:

I can't seem to get my tables to update based off of the primary table. I have a UNIT# field that is my primary key in my main table. I want to type a number in here and have it copy into all of my other tables under the field UNIT# in the other tables. Each Unit has a bunch of different data recordings so I made a table for each type of recording. I created a relationship between the tables using my main table and UNIT# as the primary key, created a query and used the [tablename].[fieldname] expression to define the "update to" field and I'm not getting anything.

This is as far I've gotten but some things I would like to do later on and maybe someone knows of a good place to find this information is



1. Having a timestamp

2. Having list boxes/text boxes autofill with maybe a check box to turn this on and off on an individual entry basis.

Thanks for any help, I appreciate it.

[tc197]

I actually solved my date problem and now that works great, still very stuck on the using 1 table to update about 6 other tables though. Thank you

[ItsMe]

Your form is based on a query? Can you open the query in DataSheet view and edit/append data?

[tc197]

I'm not quite sure what you're asking me to do, I haven't made the form yet - All I have done is made 6 Tables, and attempted to link the unit# which is common to all 6 tables together using a query. I hope that clears up what I have but I appreciate the response. I assumed that I should get my tables finalized before I design a form but if there's anything I'm doing wrong or dumb please let me know!

EDIT

Also my query will run but it says I'm updating 0 rows. I don't know if that helps at all

EDIT 2

I also created a one to many relationship between the tables using the common "unit#" field

[ItsMe]

First, understand naming conventions. You do not want to include special characters in your names. Avoid spaces between words and words reserved by Access.
https://www.accessforums.net/access/...tml#post176368

From you EDIT comment, you do not need to RUN an Action Query. Use design view to create your query objects. Start by creating simple SELECT queries (no need to use Update or Append options).

Once you have a simple SELECT query, you can test it by using Data Sheet View (look at the data retrieved. Try to edit the data. Try to create new records, etc.). Build your query in Design View and test it using Data Sheet view. Another view that is helpful is SQL view. Use SQL view to look at the actual SQL syntax. This is helpful when communicating to others what you query object is about. Someone on this form may ask, "What is your SQL?"

You are doing the correct thing by creating the tables and then the queries, before creating any forms. Test your table structure by successfully creating queries. Unable to build a functional query? Revisit your table structure.

EDIT: I would avoid building relationships via the Relationships Window. At least for now. Simply concentrate on Joins in the queries.

[tc197]

First, understand naming conventions. You do not want to include special characters in your names. Avoid spaces between words and words reserved by Access.
https://www.accessforums.net/access/...tml#post176368

From you EDIT comment, you do not need to RUN an Action Query. Use design view to create your query objects. Start by creating simple SELECT queries (no need to use Update or Append options).

Once you have a simple SELECT query, you can test it by using Data Sheet View (look at the data retrieved. Try to edit the data. Try to create new records, etc.). Build your query in Design View and test it using Data Sheet view. Another view that is helpful is SQL view. Use SQL view to look at the actual SQL syntax. This is helpful when communicating to others what you query object is about. Someone on this form may ask, "What is your SQL?"

You are doing the correct thing by creating the tables and then the queries, before creating any forms. Test your table structure by successfully creating queries. Unable to build a functional query? Revisit your table structure.

EDIT: I would avoid building relationships via the Relationships Window. At least for now. Simply concentrate on Joins in the queries.

Ok thank you very much. I'll get the special characters out and the spaces too. I'll switch back to a select query and see what I can get as well.

By table structure do you mean the way that I have them arranged? Also I thought I had to use the relationships thing so I'm glad to find out that isn't necessary.

So far when I look at what I have in the Data Sheet View the only thing appearing is my field titles but not any of the information beneath them. I'll take a look at the link you posted and see if I can get any more results. Thanks again!

[ItsMe]

You want your table structure to be Normalized.

As you build your tables, consider the Business Rules and strive for Normalization. Then build your queries. Test your queries. Revisit your tables. Consider the Business Rules and strive for Normalization (Rinse and Repeat).

As you build your tables place some data in them, while you think of what the fields will be and their types. Type some data in there. Just a couple records. When you get to your queries, you want your queries to be able to retrieve some data.

The Relationships Window is a management tool. It manages referential integrity. It does it just fine but like any automated process it has its limitations. Right now, you will have difficulties delegating responsibilities to the Relationships Window because of your lack of understanding. Later you will be a better Administrator of the Relationships Window. Hopefully, further down the road you will discover the limitations of the Relationships Window. No, it is not necessary and most advanced developers do not employ it.

[tc197]

Alright so I was able to get the query to work as a select, now as an update query. My question now is that the data only updates when I run the query -> is there a way to make the query always running? So that I don't have to manual click the query to get the values to update?

[ItsMe]

Action queries like Update and Append have a specific purpose. You will know when you need them. I believe you are after a SELECT query and nothing more. With a SELECT query you can support Data Entry. You can provide a form (GUI) to a User that will perform Data Entry one record at a time. Action queries are best suited for updating and or Appending multiple records... sometimes several, sometimes thousands, sometimes millions.

Sometimes SELECT queries do not seem to be beneficial because adding multiple tables (JOINS) will cause them to not be Updateable. One way to allow a SELECT query with a JOIN to be Updatable, is to index the fields included within the SELECT portion of your SQL. The table on the RIGHT would need to be edited. You would open the table in Design View and edit the Index property for the fields you will include in your query. Be careful not to Index too many fields. Limit the number of Indexed fields to maximize overall performance of your DB.

In your query, if you double click the line that represents the JOIN, you can edit the JOIN's properties. Cause the table on the LEFT to include all Records and the other table to only include records the match. (read the options)

When doing data entry, you typically update one table's records at a time. You then update relative tables via a second process. If you are unable to create an updatable SELECT query, you can use other tools to update relative tables/records (to maintain referential integrity). A common method is to use comboboxes on forms to update records not available to a forms recordset.

[tc197]

Action queries like Update and Append have a specific purpose. You will know when you need them. I believe you are after a SELECT query and nothing more. With a SELECT query you can support Data Entry. You can provide a form (GUI) to a User that will perform Data Entry one record at a time. Action queries are best suited for updating and or Appending multiple records... sometimes several, sometimes thousands, sometimes millions.

Sometimes SELECT queries do not seem to be beneficial because adding multiple tables (JOINS) will cause them to not be Updateable. One way to allow a SELECT query with a JOIN to be Updatable, is to index the fields included within the SELECT portion of your SQL. The table on the RIGHT would need to be edited. You would open the table in Design View and edit the Index property for the fields you will include in your query. Be careful not to Index too many fields. Limit the number of Indexed fields to maximize overall performance of your DB.

In your query, if you double click the line that represents the JOIN, you can edit the JOIN's properties. Cause the table on the LEFT to include all Records and the other table to only include records the match. (read the options)

When doing data entry, you typically update one table's records at a time. You then update relative tables via a second process. If you are unable to create an updatable SELECT query, you can use other tools to update relative tables/records (to maintain referential integrity). A common method is to use comboboxes on forms to update records not available to a forms recordset.

It's going to take me awhile to process what this all means maybe I can explain exactly what I'm doing and what my end goal is and then you can definitively point me in the correct direction?

My goal is to have a form that when opened displays data to be entered from 5 tables. I'll make up a simple example.

Breeders are our product. Dogs are the components that make up the breeder. Each Dog and breeder has a unique number tag with it. We need to measure the speed, happiness, playfulness, and size of each dog at each breeder.

I need a form that says something like

Breeder Number
Date
Person
Location
(Table 1)

Dog 1 #
Dog 2 #
Dog 3 #
etc

(Table 2, which also records the breeder # hence the update query I was trying)

Dog 1 speed
Dog 2 speed
Dog 3 speed
etc

(Table 3, which also records the breeder #)

and this continues for playfulness, happiness, and size

Now that all of this information has been entered by someone, they click add record and the data is sent to the tables. 2 months later I want to go back and check the speed of Dog #3 and find what breeder it came from. I search for Dog #3 and see it came from Breeder #1 and the recorded speed was 10 on whatever date it was.

By creating 5 Tables as described above, can I create a query to update all 5 tables based on the 1 data entry to Breeder #, create a form that displays all of this information so it can be entered at one time, and find a way to search through this information and match all of the tables information together using the breeder # (report maybe?)

I know that this is very vague and I don't even know if what I'm asking is possible/ too far over my head but I appreciate any more comments and I'll start trying to understand your previous post now. Thanks!

[ItsMe]

It's going to take me awhile to process what this all means...

Yes, it is. My posts were carefully chosen and I would encourage you to use them as reference. In other words, take a long time to read and re-read them, as well as re-read your questions.

As for this last post, I will say this type of question and how it is presented is rather common. You will be best served following the previous posts and methodology to your questions. Your previous posts were on track.

Looking at your last post, I would like to comment that you may be missing a table (this is to be expected when starting out). Some tables in DB's rarely change. Other tables are constantly appended and updated. An example of a table that rarely changes might be a table that defines a customer list, tblCustomers. Another example might be a table that defines a list of states, New York, California, etc. (tblStates). A table that changes often might be a table that logs activities. This type of table that keeps track of events, like a sale, are updated often. An example might be a new Purchase Order, tblPO.

When considering data entry, build your queries around your events tables.

You need to define an activity that will be logged in an Events table. I do not see an Events table defined. Use the Business Rules to define an Events table. Maybe your client's pets are periodically evaluated. This evaluation may be considered an activity that could be logged in an Events table, tblEval.

Store the Primary Key value of your Client, their Dog, Happiness, Speed, etc. in the Foreign Key values of your events table. Foreign Key fields in your events table should be of Data Type Long Integer. The FK should store a number relative to the Client, their Dog, Happiness, Speed, etc. The FK should represent the relative PK. JOINS are made on FK to PK, typically.

Tables that do not often get appended or receive updates will typically have a PK of Autonumber type and no Foreign Key fields. Tables that log events/activities typically have a PK of AutoNumber type and several FK fields of Long Integer type. When you open an events table, it should not be intuitive to humans because it will be mostly numbers that represent records from other tables. The other (related) tables will be slightly more intuitive because they will hold descriptions in fields of type Text.

[tc197]

Alright sounds good, thank you very much for all the information. I'll most likely be working on this next week as well so when I move further along and run into some new questions I'll be back. For now I'm going to start back up at the top of this thread and try to find a clear plan to follow. Thanks for all the help, I appreciate it.

[tc197]

So I've been listening to what you said and kind of experimenting but now I'm really stuck. My forms won't let me input any data. My text boxes won't let me type, the combo boxes will drop down but won't let me select... any advice? The form is set to allow edits and everything and theres no locks. Did I relate the table to the forms improperly maybe?

[June7]

A form can be used to create record into only 1 table. Are you using form/subform(s) arrangement?

[tc197]

A form can be used to create record into only 1 table. Are you using form/subform(s) arrangement?

I used the form wizard and I was under the impression that it would only work if I had correctly related all of my tables. The wizard loaded everything for me but is there separate steps for sub forms?

EDIT
So I think i'm using subforms but honestly don't really know.