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 19th, 2008, 02:04 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 New to Access & DB

Hi all!

I'm new to programming, DB's and Access. The only programming I have done is on rather ancient technology, mostly in BASIC, and it's variants like QBASIC, etc. I'd love to be able to learn how to do VB, C, C#, C++, ASP, .NET, etc.

I have the need to make a hobby-related database which contains data from 10-15 years of published data containing a small number of related fields. I tried to learn Assembly, like the type used in Microsoft Macro Assembler (MASM), but got stuck.

I also would like someone's help setting it up. I have Microsoft Office 2007.

Thanks,
Greg

 
Old November 19th, 2008, 01:41 PM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

I think you best bet would be to limit yourself to VB.NET, VBA and VBScript to start, which are all very similar to BASIC, QBASIC. I would forget C languages for now unless you really have the need, since it will only confuse you as you are learning Access.

I would suggest using one of the wizards to create your first database, and then modify it from there. Sorry about 2007, though. Most of us I think are not liking it very much.


mmcdonal

Look it up at: http://wrox.books24x7.com
 
Old November 19th, 2008, 04:35 PM
Friend of Wrox
 
Join Date: Jun 2008
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

One big question: Do you intend to have the DB accessible via the Web?? Or is it just meant to be for your own use, standalone from everything else?

If the latter, you can likely do all you need directly in Access, using Access's own forms and macros and what not. MMcDonal will be able to help you with that route (I don't use Access stand-alone). You may find that much of what you want to do doesn't even require programming or, at the most, needs just a few snippets of code here and there.

***********

But if you want the data accessible on the Web, then yes, you'll need to learn either ASP or ASP.NET. ASP is pretty much like Latin: A dead language that still has its uses for some religious (i.e., "fans of ASP") groups. So you might as well bite the bullet and learn ASP.NET.

Fortunately, the tutorial materials available for ASP.NET are nothing short of wonderful. And, to boot, there is a truly great development tool that is free: Visual Web Developer Express. You can find it via
   http://msdn.microsoft.com/Express

And while you are there, poke around and find all the great tutorial material. "Learning tracks" for beginners and intermediates.

And then you can also check out http://www.asp.net/Learn for many many more tutorials, including hours of video.

Note that in either case you will have to set up the Access DB and populate your tables with data. And how hard that is depends *A LOT* on what your data looks like, now. If, for example, you already have it in a spreadsheet, importing it to a DB is pretty trivial.
 
Old November 19th, 2008, 08:32 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

Hi again!

Well, Initially the data will be for my own use. After a while, I may publish it to the web, or make it available as a download, or on disk.

The data comes from volumes of this book, from 1986-2009:

http://www.amazon.com/Passport-World...7139465&sr=8-1

The data will be in the format:

Frequency in the format xxxx.x (where x = a integer value of 0 through 9)

Some form of showing that this frequency may be "variable" as shown in the text.

Some form of showing whether this is a new and/or changed schedule.

Country of Origin: i.e. U.S.A.

Station ID: i.e. Voice Of America

World time in GMT or UTC: ie 0 through 24h (23h,59m,59s) UTC.

Transmitter or Antenna location, ie a town, state,city, province, etc.

Transmitter power, in kW. ie 100kW

Mode of Transmission, ie AM.

Alternative Frequency, i.e. 7995kHz

Season, ie Summer, Spring, Fall/Autumn, Winter

Language of Transmission: ie, Farsi, English,Spanish

Some form if indication that the transmission is "Jammmed"

Transmission aimed at: i.e. S. Africa, E. North America, etc.

And a comment about which volume year and/or years this entry appears. Some other misc. comments.

Oh yea I downloaded the DVD iso at:

http://www.microsoft.com/express/

Thanks,
Greg

 
Old November 20th, 2008, 08:45 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

It looks like you will need this sort of structure:

The data will be in the format:

Frequency - Number with 1 decimal place format (not an integer)
Variable - Yes/No
New/Changed - TWO check boxes if the item can be both new and changed indicated by "and/or" in you post.
Country of Origin - look up to a table with country names
Station ID: Text field
World time in GMT or UTC: This could be an option group with auto populated combo boxes based on whether you choose one time or the other.
Transmitter or Antenna location - combo box?
Transmitter power - number
Mode of Transmission - option group or combo box
Alternative Frequency - number field
Season - option group
Language of Transmission - probably a multi-select list box? look up to Language table
"Jammmed" - Yes/No
Transmission aimed at - combo box look up to continent table
Volume Look Up to volume table, which will contain the year the volume was published.
Comments - memo field.

Anyway, that is one way to get started.

Did that help?


mmcdonal

Look it up at: http://wrox.books24x7.com
 
Old November 20th, 2008, 02:03 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

mmcdonal,

   Yes, that did help. The number in time format for the field "World Time in GMT or UTC" should show the entry's' program time stop and start times, i.e. from 13 hours UTC to 22 hours UTC.
Other than that your format is correct.

   Sometimes the frequency data in the original texts entry will say something like 7995.3v, which means that the frequency varies in some form.

   Transmitter Power should show in kW (kilowatts)

   The data should also show what days of the week, Sun. thru Sat. that the program is heard.

    Transmission aimed at is sometimes published in the text as something like, C. Africa & S.E. Asia, denoting this Transmission is aimed at two areas, and rarer the entry in the text shows three target areas.

    The comment field could show that the entry is shifted an hour later or earlier during mid year, and information on any network. The network is shown as one of several denotations, ie. DS, DS-1, DS-2, DS(FEEDER, DS-TEMPORARY, DS-IRREGULAR, DS-1(FEEDER), DS-LOCAL, etc.

    The only other item to add would be whether or not the entry is "Irregular", as denoted by "Irr" in the text.

    Ok, so how do I get started on building this?

 
Old November 20th, 2008, 09:42 PM
Friend of Wrox
 
Join Date: Jun 2008
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

Ummm...you get started by creating a table with the appropriate field names and the appropriate types for each field.

Bring up Access. Ask it to create a new database. Give it a name and findable location.

Then ask to "Create Table in Design View".

And it should be obvious.

A few cautions: *ALWAYS* use a DATETIME field type to hold dates and/or times. But then read this:
http://www.aspfaqs.com/aspfaqs/ShowFAQ.asp?FAQID=189

So just remember that the format in which you enter a date/time and/or subsequently display it has NOTHING to do with what is remembered in the DB. This *can* be a HUGE advantage: You can thus easily convert
UTC times to local times as part of the display/report process.

And as with date/time values, where *AT ALL* possible, do not store numbers as text, either. Transmitter power in KW? Fine, so in the DB just choose a NUMBER type and an INTEGER subtype, for example. I'd do the same thing for frequency (except then it is NUMBER and DOUBLE subtype) and put the "V" in as a separate boolean (YES/NO) field. [I just noticed that MMcDonal already suggested this...see? great minds run in the same gutters!]

For things like "days of the week heard", you could either use a separate boolean field (Yes/No in the table designer) for each day or you could use a single text field that you would have hold numbers (e.g., "246" would indicate Monday+Wednesday+Friday). There are advantages to both approaches.

Same decision for the 4 searsons, for example.

If fields hold "standardized" strings, consider using "lookup tables" for the strings, so that your table just holds an index into the lookup table. You can find more about lookup tables by just using HELP in Access.

In general, *NEVER* save delimited text in a single field. In your example, where you have "Aimed at" with 2 or possibly 3 values, consider using a separate table, where the ID from the main table is the foreign key in the AimedAt table.

Access really is pretty easy and intuitive to use, once you've played with it for a while. Give it a shot! Come back when/if you get stuck.
 
Old November 21st, 2008, 12:55 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

Well, so I just start Access 2007 and use a wizard or do I go to something like File>New>Create New Database. Does it matter where I store it? I have a 4gb USB stick.

I wanna do some reports, queries, etc. so that I can be able to type in a searh like a certain frequency range, location, 1/2hr to 1hr time periods (in GMT or UTC of course..) etc etc and have it give me a "whatif" type result.. For instance let's say that I wanted to know what I could listen to during a particular time on a given day, in a certain frequency range, etc, and then as well as have it relate to data stored in my logbook, which has a similar format, so that it could use that data to be even "smarter" as it returns results. Most people in this particular hobby use Excel for a logbook, and I probably will too.

So could you display and/or record the time in both Local Time and GMT or UTC? For instance My local area is GMT-8 (Pacific Time Zone).

I also thought about some day at a later date, merging data from another book, the World Radio and TV Handbook, which is also published yearly. For many years I've bought both books.

Once I get to "Create Table In Design View", what do I do from there?

I also am planning on having it accessible, at some point in time, via a OpenOffice Base .odb file.

Edit: I also forgot to ask about adding a section of the database, queryies and/or result for sections of fequencies called "meter bands" which normally appear as something like "49m" which would denote the 49m HF band.

Greg
 
Old November 21st, 2008, 01:21 AM
Friend of Wrox
 
Join Date: Jun 2008
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

Haven't used Access2007; have used Access97, Access2000, and Access2003.

Generally, with any of those, when you first start them up they ask you what you want to do and one of the options is "create new database". And then, once you've given it a name and location, you proceed as I outlined. TRY IT! The worst that happens is you toss it out and start again. Surely take less time to try it than to pass a half dozen messages back and forth here.

I would *not* recommend storing the DB on a USB stick when it comes time to do any heavy searches or other queries. USB memory is usually slow, compared to a hard disk drive. But during development and data entry? Sure, why not.

As for date/time: If you always enter them in UTC form (without the UTC on them, by the by) then all you have to do to get them back in (say) GMT-8hours is
    SELECT ..., DATEADD('h',-8,anyStoredDateTime) AS localDateTime, ...

DATEADD is a handy little function that allows you to add (or subtract, using a negative value) any kind of time increment (you use 'N' for minutes, to avoid confusion with 'M' for months) to any datetime value.

That's just one of dozens and dozens of hand functions in Access.

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

Yes, you can actually use data from an Excel spreadsheet directly in a SQL query from Access. Not too hard to do, at all. Up to you whether to do that or, since you are already creating at least one table in Access (and I recommend several tables, not just one!), just add another Access table with a handy data entry form.

&&&&&&&&&&&&&&&&&&&&

Everything you want to do seems reasonably easy, but the first step is to create the database and then the table to start entering data.
 
Old November 21st, 2008, 02:17 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

Well, I'm thinking that the final data will reside on at least a CD-R or RW, but maybe a DVD.

I've also used YouTube to as one more additional tool for tutorials on this, so I've already looked at a few videos on Access 2007. I'm also reading all the eBooks on .pdf (Dummies books, Missing Manuals, etc.)

How does that type of query pass through SQL?

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

At one time I had Access 97 installed as part of an installation of Office 97 (still have the disk too..) ages ago and never used it. Wished now that I had learned it and used it.

Note I'm just getting started and trying this all out as we speak.. Multitasking.. :D

Greg









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.