Discuss "Basic Questions" in the Microsoft Access forum.

Simple Questions:

1. Can a form handle Multiple table input and I dont mean a subform?

2. Why have people instructed me that data entry should be done through queries and not tables.

anyone is welcome to add questions to this list that they think are basic (requiring yes or no answers or a short explanation) Thanks who ever answers and asks i suppose

• Records in a table have no order.
• Using a query as a record source allows you to set an order.
• You may not need all the table fields returned in your form
• You may want to add more than one table to your form, you can do this by creating a simple join in the query. Although I recommend this only for viewing data already inputed. Data entry against multiple tables is what sub forms are for.

__________________
If i helped you, make me famous by clicking the

to add to what AOG stated, you can make subforms look like part of the main form (meaning it doesn't have to be in datasheet view and the subform doesn't have to be "sunken" in the format, it can be "flat" with invisible borders)

A query will also allow you to do a calculation before it gets to the form. Also, looking up from a query is much faster than doing it from dlookups in your form. Another aspect to look at: If you have 3 or 4 very complex calculations done in your query that you are going to use in multiple forms, you only have to do it once, whereas in a form you'll have to make an unbound textbox and redo the formula again. One more thing, sometimes you need embedded queries, can't do the same thing easily from a form.

Thank you for that explaination. And while we are on "joins" has anyone done a good thread on joins? My biggest problem as i have been learning i believe is that there are so many concepts to learn. I think my focus as I learned (being an excel guy) is the "form" because it would present the info. This has lead to my confusion. I guess what I would like to know is how a many to many relationship can be represented in a form. I realize that this is just general and not too specific so it might be hard to explain without examples

That was also very helpful sbenj69

Basically you have

• Inner Join - Selects data from both database tables that have matching values
• Outer Join - Selects all data from one db table and only those with matching values from another

See this link for more details regarding right and left outer joins

Have a look here for a good explanation on query joins >

<<Query Joins - Inner Joins & Outer Joins | Database Solutions for Microsoft Access | databasedev.co.uk>>

OK, let's think about a Many-To-Many (M-M) relationship and how it might be displayed in a form. Think about a school that has Students and Classes. Clearly, each Student can take Many classes and each Class can have Many students -- your classic M-M relationship. How might you want to display this in a form?

I can't think for a useful way that would display the entire M-M relationship. It could be done, but I doubt it would be very useful (I'll get to a query that 'flattens' this relationship in a bit). Usually, the form designer would choose of the table in the M-M relation as the Master table -- let's say Students -- and display a list of Students in a Form. When a specific Student is selected (clicked) in the display, then the Classes that the student is taken could be displayed in a Subform, or Child table. This is a VERY common interface (called variously Form/Subform, Master/Slave, Parent/Child, Independent/Dependent and probably a few other). Selecting a new Student in the Master table would then display a different Child table with the Classes for THAT student. In short, you (the form designer) would choose a table to become the 1 side of a 1-M relationship (any M-M can 'devolve' to a 1-M if only 1 record is chosen). Obviously, you could reverse this and have Classes/Students rather than Students/Classes.

Now, how would you implement a M-M relationship in the Database. A M-M actually requires 3 tables; one each for each side of the relationship (A Student Table and a Class Table) and a third table to relation the two together (let's call it the StudentClass table). Now, assume that both the Student and Class table have an IDENTITY (autonumber) column, called StudentID and ClassID. The structure would then be something like:Now, let's say that Bob registers for History, Physics and Basket Weaving. We make no changes to the Student or Class table, but rather add the relationship to the StudentClass table.With is structure, it's very simple to determine what classes Bob is taking, and what Students are registered for (say) Math. Use a JOIN query.That might look intimiating at first, but if you draw the tables out a paper and JOIN them together as in the above query, it WILL start to make sense.

On final tidbit to help you wrap your brain around JOINs. In a 1-M relationship, the table on the MANY side will ALWAYS have a column that relates to the table on the 1 side -- this called a Foreign Key. This common column is the one that is used in the ON clause of the join (tho it quite possible to use 2 or more columns).

__________________
Wolffy
------------------------
Opinions expressed are my own and do not necessity reflect those of any sane person. Any code provided is intended to be an example and is provided AS IS. Rework for your specific environment may be required. Void where prohibited by law. Not valid in California. Your mileage may vary.

When someone is building a table that houses financial data such as a checking account arrangement where there are debits and credits that equal to a current sum, is it common to query the whole table for the current sum (Current Balance) or is there a way to, I suppose archive much of the old info for performance reasons.

Only the transactions would be stored in the table, the current balance is calculated in the query. You're table would need to be very large before performance is an issue. (Assuming proper indexes, etc.). The debit or credit would be in the SAME field (negative for a DR, positive for a CR). So to find the current balance:

My thoughts were to have them in different fields as a total credits and total debits and then subtract debits from credits. There would a reason to query them separately and get total debits etc. Do you think that is poor table design?