New to Access & DB

Old Pedant · November 21st, 2008, 05:34 PM

I am *NOT* an Access user, per se. I create Access databases, but then I use ASP or ASP.NET (web code) to update and query them. In other words, clearly not "standalone".

MMcDonal is much more experienced here than I am (as are others, I'm sure) and you might find that the Access forums at MSDN have more knowledgable users, too.

Having said all that...

> How does that type of query pass through SQL?

Huh?? You mean my
SELECT ..., DATEADD('h',-8,anyStoredDateTime) AS localDateTime, ...
query?? It doesn't "pass through" SQL. That *IS* SQL. You would, presumably, use that snippet in your reporting, when you displayed data to the user. (You'd also, presumably, find out their local time zone and use the proper offset instead of the constant "-8" that I used there...but now we're starting to get into at least "Intermediate Reporting 201".)

> What type of form setup or layout would be best, given what I want?

Well, I think MMcDonal started you off on the right foot. He was indeed describing what your data entry form would look like. For example, he wrote:
"Country of Origin - look up to a table with country names"

In other words, your form would have a drop down list that would reference an already-created lookup table. There's nothing special about a lookup table from the SQL viewpoint, but Access-the-program ia able to recognize how to effectively use one in your forms and reports.

A lookup table typically has two fields: An autonumber id field and a text value field. So then the drop down in the form shows all the possible text values and, when you click on one, it stores the id [the number] into the field in your main table. This is in support of "Normalization", which is another topic worth reading about when you have time. It's the key to efficient databases.

gbowne1 · November 22nd, 2008, 02:03 AM

Ok, so where are the MSDN "Access" forums? There's so many MSDN type pages..

Quote:

quote:> How does that type of query pass through SQL?

Huh?? You mean my
SELECT ..., DATEADD('h',-8,anyStoredDateTime) AS localDateTime, ...
query?? It doesn't "pass through" SQL. That *IS* SQL. You would, presumably, use that snippet in your reporting, when you displayed data to the user. (You'd also, presumably, find out their local time zone and use the proper offset instead of the constant "-8" that I used there...but now we're starting to get into at least "Intermediate Reporting 201".

Yeah, that's what I was asking about passing throug SQL. In the back of my brain I was thinkin' I maybe had to have some special program or something. A "duh" moment for me. (I have ADD/ADHD)

I'm the type of person that needs some sort of a visual and/or written step by step process of doing things, especially when I'm learning.. or it will take me much longer to "get it".

When I get to that point, where and how do I type in the code? is it entered just as you have it written?

Greg

Old Pedant · November 22nd, 2008, 08:49 PM

Starting point on MSDN:
http://msdn.microsoft.com/en-us/office/aa905410.aspx

***************

My code was an *example*. You'd use the *form* of it, but you'd change the names to match your actual situation. I was hoping that a name such as "anyStoredDateTime" would give you the idea that it's just a field name. A variable name, if you will, in QBASIC parlance.

Have you ever done a SELECT query, at all?? If not, then get to that stage before you worry about what I showed.

gbowne1 · November 25th, 2008, 11:23 PM

No, I've not used SELECT at all.. well notyet anyhow. But.. I get the idea now.

I'm on the MSDN Access Forums now. I'm still sort of wondering where all my databases' relationships lie.

Greg

Old Pedant · November 26th, 2008, 01:00 AM

Relationships are something YOU create. You don't have to have relationships, though it's usually a really damned good idea to have them!

In Access, the easiest way to create a relationship is using the Access built-in tools.

Remember, I don't have Access2007, so what I will tell you is for Access2003 and may not be *quite* the same in 2007.

Steps:
(1) Open Access.

(2) Create your tables.

For a simple example, let's say we have a table of people:

Code:

    TABLE: PEOPLE
    personID  AUTONUMBER
    firstName TEXT
    lastName  TEXT
    email     TEXT
and a table of things that each person likes
    TABLE: LIKES
    personID  NUMBER (INT)
    likes     TEXT

(3) Click on TOOLS menu.
(4) Click on RELATIONSHIPS menu item (you might have to click on the double down arrow to find it)

A window appears with a dialog box that shows all your tables. Click on each of your two tables (or more if you have more) and then click on ADD, until all your tables are displayed. Then you can click CLOSE on that dialog.

(5) Find the two fields in two tables that are supposed to have matching values. (In my example, find personID in both tables.) Click on ONE of those fields and then DRAG the mouse (with the button still down) to the other field.

(6) Another dialog box should appear. Titled "Edit Relationship".
Check the checkbox labelled "Enforce Referential Integrity".
Optionally, click on "Cascade Delete Related Records".

PRESTO! You have defined a relationship.
As a bonus, you also now have a nice "Entity-Relationship" (abbreviated "ER" or "E-R") diagram of your database!

Now it's your turn to do more reading and learning on your own.

gbowne1 · December 5th, 2008, 05:29 AM

OldPendant,
  I Sent you a copy of what I created. If I did anything incorrectly, You're free to change it per the format listed in this Topic above.

  Thanks for all your help.

  Greg

gbowne1 · December 13th, 2008, 02:36 AM

Hello again! :-)

I got most of the database set up and the about 3/4 of the final data entered, and what I think is a fairly decent form built. I originally had some minor troubles with it Thanks for your help again OldPendant.