How to create database & table

[Ellen]

Hello: Can someone tell me how to create a database/table,
that will use the following functions. I am not sure how
to design it. This is just a sample, database/table I need
to create to test queries. The following functions I will
use are as follows: LCASE(), Left(), Len(), Mid(),
Right(), RTrim(), Space(), TrimS(), UCase(), DateAdd
(), DateDiff(), Avg(), Count(), Max(), Min(), SumS().

Help is very much appreciate,

Ellen

 

[Cheryl Fischer]

Hello Ellen,

Homework? I would suggest the following:

1. Open MS Access and click Help. Then click the Contents tab and navigate
to the "Getting Started" topic - subtopic: "Ways to get started if you are
using a database for the first time. This topic will explain the purposes
of a database and how each of its different parts (tables, queries, reports,
etc.) are used.

2. Then, using the MS Access menus, create a new database.

3. Within the new database, create a new table and add fields to it. For
your testing, you will need a couple of Text fields, a couple of Date/Time
fields and a couple of Number fields.

4. Add records to your table, so that when you test the functions you have
listed, your queries will work.

5. If you need to read-up on the purposes of each of the functions you
listed, open VBA by pressing Alt-F11. Then click VBA Help. You can use
the Answer Wizard to enter each of the functions to read about it.

6. Create a few queries on your table to test the behaviors of each of the
functions.

 

[Tim Ferguson]

Can someone tell me how to create a database/table,
that will use the following functions. I am not sure how
to design it.

CREATE TABLE TestTable
( FName TEXT(8) CONSTRAINT pk PRIMARY KEY,
BirthDate DATETIME NULL,
NumKids INTEGER NULL
);

This is just a sample, database/table I need
to create to test queries.

INSERT INTO TestTable (FName, BirthDate, NumKids)
VALUES ("Alice", #1966-09-30#, 2);

INSERT INTO TestTable (FName, BirthDate, NumKids)
VALUES ("Brenda", #1967-10-12#, 1);

INSERT INTO TestTable (FName, BirthDate, NumKids)
VALUES ("Cynthia", #2002-02-11#, NULL);

INSERT INTO TestTable (FName, BirthDate, NumKids)
VALUES ("Deborah", #1909-07-08#, 7);

The following functions I will
use are as follows: LCASE(), Left(), Len(), Mid(),
Right(), RTrim(), Space(), TrimS(), UCase(), DateAdd
(), DateDiff(), Avg(), Count(), Max(), Min(), SumS().

SELECT LCase(FName) AS AllInLittleLetters,
LEFT(FName,1) AS IntialLetter,
LEN(FName) AS LengthOfName,
MID(FName, 2, 2) AS MiddleOfName,
RIGHT(FName,1) AS EndOfName,
RTIM(FName) AS Redundant,
SPACE(3) AS Empty,
TRIM(FName) AS StillRedundant,
UCase(FName) AS AllInBigLetters,

DATEADD("y",10,BirthDate) AS TenYearsAfter,
DATEDIFF("y", BirthDate, DATE()) AS YearsOldNearly,

AVG(NumKids) AS MeanKids,
COUNT(*) AS NumPeople,
COUNT(NumKids) AS NumParents,
MAX(NumKids) AS Strongest,
MIN(BirthDate) AS Oldest,
SUM(NumKids) As NumChildren

FROM TestTable
ORDER BY FName;


Hope that helps


Tim F

 

[Duane Hookom]

Tim,
You get an A+ on this assignment ;-)

 

[Ellen]

There are good people, Thank You.

-----Original Message-----
Tim,
You get an A+ on this assignment ;-)

--
Duane Hookom
MS Access MVP





.

 

[Ellen]

You are an angel.

Ellen

-----Original Message-----


CREATE TABLE TestTable
( FName TEXT(8) CONSTRAINT pk PRIMARY KEY,
BirthDate DATETIME NULL,
NumKids INTEGER NULL
);



INSERT INTO TestTable (FName, BirthDate, NumKids)
VALUES ("Alice", #1966-09-30#, 2);

INSERT INTO TestTable (FName, BirthDate, NumKids)
VALUES ("Brenda", #1967-10-12#, 1);

INSERT INTO TestTable (FName, BirthDate, NumKids)
VALUES ("Cynthia", #2002-02-11#, NULL);

INSERT INTO TestTable (FName, BirthDate, NumKids)
VALUES ("Deborah", #1909-07-08#, 7);

SELECT LCase(FName) AS AllInLittleLetters,
LEFT(FName,1) AS IntialLetter,
LEN(FName) AS LengthOfName,
MID(FName, 2, 2) AS MiddleOfName,
RIGHT(FName,1) AS EndOfName,
RTIM(FName) AS Redundant,
SPACE(3) AS Empty,
TRIM(FName) AS StillRedundant,
UCase(FName) AS AllInBigLetters,

DATEADD("y",10,BirthDate) AS TenYearsAfter,
DATEDIFF("y", BirthDate, DATE()) AS YearsOldNearly,

AVG(NumKids) AS MeanKids,
COUNT(*) AS NumPeople,
COUNT(NumKids) AS NumParents,
MAX(NumKids) AS Strongest,
MIN(BirthDate) AS Oldest,
SUM(NumKids) As NumChildren

FROM TestTable
ORDER BY FName;


Hope that helps


Tim F

.

 

[Ellen]

Thank You

Ellen

-----Original Message-----
Hello Ellen,

Homework? I would suggest the following:

1. Open MS Access and click Help. Then click the Contents tab and navigate
to the "Getting Started" topic - subtopic: "Ways to get started if you are
using a database for the first time. This topic will explain the purposes
of a database and how each of its different parts (tables, queries, reports,
etc.) are used.

2. Then, using the MS Access menus, create a new database.

3. Within the new database, create a new table and add fields to it. For
your testing, you will need a couple of Text fields, a couple of Date/Time
fields and a couple of Number fields.

4. Add records to your table, so that when you test the functions you have
listed, your queries will work.

5. If you need to read-up on the purposes of each of the functions you
listed, open VBA by pressing Alt-F11. Then click VBA Help. You can use
the Answer Wizard to enter each of the functions to read about it.

6. Create a few queries on your table to test the behaviors of each of the
functions.


--
Cheryl Fischer
Law/Sys Associates
Houston, TX




.

 

[Ellen]

Tim, I do not understand the CONSTRAINT, I am assuming
that the pk Primary Key is where I set on FName field.
I am not understanding the Null either. On the Birthdate,
is Date/time?, and Null?
On NumKids, set Number format INTEGER? And the Null,
confuses me., along with CONSTRAINT. I am also confused
about queries, Do I use Select Queries for all functions.
I know I am asking alot, but I am learning. I have been
reading and using help wizard, having a hard time
comprehending, I am learning at home.
Thank you for your help.
Ellen.

 

[Tim Ferguson]

You get an A+ on this assignment ;-)

Oooooh noooo:- have I just blown someone's homework? Stupidly, I did not
stop to think...


Tim F

 

[Tim Ferguson]

I do not understand the CONSTRAINT, I am assuming
that the pk Primary Key is where I set on FName field.

Have you looked up help for the CONSTRAINT keyword? It is a standard part
of the SQL language for creating a table. You can begin by reading the help
files for CREATE TABLE, which will tell you all about what comes next.

I am not understanding the Null either. On the Birthdate,
is Date/time?, and Null?

Any field (nearly...) can be either Required (NOT NULL) or Not Required
(NULL). I can't remember which is the default, so it's always better to put
in a NULL or a NOT NULL with each field definition. You don't need to put
it in the first one because it's the primary key field and by definition
you can't have nulls in a primary key.

On NumKids, set Number format INTEGER? And the Null,
confuses me., along with CONSTRAINT.

See above. It's an integer because you can only have whole numbers of
children (well, usually...). And it's NULLable becuase sometimes it's
unknown -- not knowing how many children someone has is not the same thing
as knowing that he or she is childless.

Do I use Select Queries for all functions.

You use SELECT queries for retrieving information from a database: INSERT
queries put data in and UPDATE queries change records that are already
there.

I have been
reading and using help wizard, having a hard time
comprehending,

Access Help is notorious as the second most useless thing since radio for
the deaf. Once you know what to look for, then there is good reference
material there, but finding something new is an awful process.

For learning, I would start with a beginners book, from the likes of Wrox
or Que and so on, or even Access for Dummies. That will get you up to speed
with the user interface and the very basics of database design: after that
you will probably need something more general about Relational theory,
normalisation, and so forth. That will also have information about standard
SQL like your questions above. Stick around here for details that you may
be stuck with.

I am learning at home.

Phew!

All the best


Tim F

 

[Douglas J. Steele]

Oooooh noooo:- have I just blown someone's homework? Stupidly, I did not
stop to think...

Nah, it's okay Tim, they're still going to have to do a bit of work to get
it to work. You had at least one typo that I saw, and you forgot the GROUP
BY fields <g>

 

[Tim Ferguson]

Nah, it's okay Tim, they're still going to have to do a bit of work to
get it to work. You had at least one typo that I saw, and you forgot
the GROUP BY fields <g>

Oh you are hard...

I haven't spotted the typo yet (going off to test after this) and I didn't
mean to group anything.

GOT IT! RTim instead of RTrim; and I should have put the multirow functions
in a separate query from the aggregate ones. So I guess it's an A- after
all :-(


B Wishes


Tim F