Wrox Programmer Forums
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old November 21st, 2008, 05:34 PM
Friend of Wrox
 
Join Date: Jun 2008
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

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.
 
Old November 22nd, 2008, 02:03 AM
Authorized User
 
Join Date: Nov 2008
Posts: 16
Thanks: 3
Thanked 0 Times in 0 Posts
Send a message via Yahoo to gbowne1
Default

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 November 22nd, 2008, 08:49 PM
Friend of Wrox
 
Join Date: Jun 2008
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

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.
 
Old November 25th, 2008, 11:23 PM
Authorized User
 
Join Date: Nov 2008
Posts: 16
Thanks: 3
Thanked 0 Times in 0 Posts
Send a message via Yahoo to gbowne1
Default

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 November 26th, 2008, 01:00 AM
Friend of Wrox
 
Join Date: Jun 2008
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

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.
 
Old December 5th, 2008, 05:29 AM
Authorized User
 
Join Date: Nov 2008
Posts: 16
Thanks: 3
Thanked 0 Times in 0 Posts
Send a message via Yahoo to gbowne1
Default

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

 
Old December 13th, 2008, 02:36 AM
Authorized User
 
Join Date: Nov 2008
Posts: 16
Thanks: 3
Thanked 0 Times in 0 Posts
Send a message via Yahoo to gbowne1
Default

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.





Similar Threads
Thread Thread Starter Forum Replies Last Post
Memorise ODBC Username & Password in Access DB Roly Reefer Access VBA 23 January 20th, 2012 02:32 AM
capture file path&name and store in a access DB paul20091968 Access VBA 1 April 5th, 2006 02:17 AM
XML from a DB recordset (removal of <>) Thodoris XML 3 July 13th, 2004 12:28 AM
Using example of Chpts 12& 13 to access new DB cvc505 BOOK: Beginning ASP 3.0 0 October 17th, 2003 04:53 PM
ASP form to email & update Access db seala Classic ASP Basics 3 July 22nd, 2003 01:39 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.