Re: Populating a list -- learning Access

Hi Kass,

Thanks, Kass. I am flattered, of course. Like you, I am constantly learning more about Access. Although I have been using Access for more than a decade and programming for about 30 years, there is soooo much to know, Access is truly a remarkably powerful application. I am in awe of others who are so incredibly amazing and they are MY mentors. I do not often ask questions, but I do search and read posts written by the many people that I admire.

Until a couple years ago, I didn't even know public forums like this existed! Most of what I know is self-taught. I am one of those people who got used to learn in alphabetical order :) Back when software came with manuals, before I used an application, I read the documentation from cover-to-cover. I like to read printed pages better than a screen, so when I really want to understand something, I print it out and take notes in the margins.

If you start reading the help in Access from the beginning of the Table of Contents, you will find that it is very well written.

There is really no way for me to help you directly on the forums -- what I mostly do is look for unanswered posts to see if I can be of assistance. I will keep my eyes peeled for your name, though -- but I cannot make any promises as my volunteer time is limited and if someone else is already giving you assistance, I do not want to step on any toes -- if I can add something, I will if I see it.

My business is custom training and programming -- mostly over the phone and through email. If you contact me directly (email address in my siggy), I assume you are interested in information about that.

I am writing a book on VBA and send the chapters that are done to anyone who requests it -- email me if you want them.

I am thrilled that you are getting a grip on SQL and happy that I put you on your path. It is kinda funny -- I started using databases in the early 80's with dBase and, for years, never knew that I knew SQL! I heard the term, but it was like a mysterious cloud! When I finally LOOKED at an SQL statement, I laughed! ... couldn't believe that I was so intimidated by it when it was something I already knew -- just had never known what to call it!

Programming itself is simple logic -- the main thing you need to be successful is a strong foundation and a logical approach. Once you become familiar with syntax, it makes total sense! With Access, the most important thing to get right is the data structures -- that is like the foundation of your building -- it matters not what color the walls are (the reports) -- the foundation is what determines how high you can go.

Another thing about Access -- it is OBJECT-ORIENTED. Just like in the real world, every object has properties and methods.

Properties describe an object
Methods define actions an object can do

For instance, you are a human and have properties such as hair color, eye color, height, weight, ... and methods such as eat, run, jump,...

If you become familiar with the different types of objects that Access can use and the properties that define them and the methods they can do (and what triggers them), you will be on your way!

When you are in the design view of anything, keep the properties window showing. Turn on/off the Properties window --> View, Properties from the menu

Then click on various objects.

You can get help about any property by pressing F1 while in the property you want more information on.

If you are on a form or report, the most important property is the NAME, because that is how you refer to it in code. If the name is ambiguous, change it!

Explore the property ***. Get familiar with how properties are grouped on the tabs (where applicable) and the different properties available for different objects.

For general help about Access, I find it interesting and informative to read the help starting from the beginning of the Contents. In fact, if you have the desire to print a ream of paper, it would be good to print it like a book and read it.

I wish you continued success!

Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day ;)

remote programming and training
strive4peace2006 at yahoo.com

*

Kass wrote:
Crystal,

Thanks! I had made a report already and figured out about adding the "Product Footer" as you say. But this put the Product on one line and the Product count on the line below it. This was workable, but I really wanted them both on the same line for clarity and to save space. So after reading your response, I dragged the Product field into the Product Footer section and it is just perfect! Thanks for all your help! This is working just as I need it to! Again I appreciate all you have helped me with and I'm so excited because thanks to your SQL summary info, I'm finally starting to understand this better.

If I have a problem in the future, is there a way to direct a question to you. I don't mean to pester you, but you seem to explain things so I can understand them. I have always appreciated the responses on the board, whether my question or just searching, but sometimes I just don't click with the way some of these guys explain things. If it is not possible or feasible, I certainly understand... just thot I'd inquire.

Thanks for everything!

Kass

"strive4peace" <"strive4peace2006 at yaho" wrote:


Hi Kass,

thank you :)

make Product one of your report groupings and choose YES for Group footer so you create a section

from the menu --> View, Sorting & Grouping

hide the detail

in the Product footer, put the product and the count


Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day ;)

remote programming and training
strive4peace2006 at yahoo.com

*

Kass wrote:

Crystal,

You have been so helpful and I really appreciate it! I don't mean to skip your responce below, but thanks to your help with SQL, I was able to get the query to pull the info the way I needed. I have one report complete, "Book & Activities Report". It takes a summary from a select query and gives the following data:

Book Name : Pawnee Elementary Grade 5
Activity 1 - Product 1
Product 22
Product 3
Product 18

Activity 2 - Product 22
Product 44
Product 66

Activity 3 - Product 3
Product 18
Product 55
Product 12
Product 22
ECT...

Now I need a similar report, Book & Parts Report. I need it however to make a count of the Products... something like this:

Book Name : Pawnee Elementary Grade 5
Product 1 1
Product 3 2
Product 12 1
Product 18 1
Product 22 3
Product 44 1
Product 55 1
Product 66 1

The report will put the Products in order, with no gaps (I tried Hide Duplicates... but it leaves gaps) and a summary count of each of the products. This will help my client not only know what which Activities and what Products go into which Books, but also give them a summary of the Products and how many of each go into each Book.

If you know this off hand, let me know. If it is alot of trouble on your end, I'll just keep doing some digging. Seems like I've done this before, but can't remember if I had to do it within a query or in the report itself.

Take Care!

Kass

"strive4peace" <"strive4peace2006 at yaho" wrote:



Hi Kass,

you are welcome :)

In order to help you, I need to better understand your structure. This is what I have surmised -- please make corrections...

*Products*
PID, autonumber
Product, text

*Activities*
AID, autonumber
Activity, text

*Kits*
KitID, autonumber
AID, long integer
Kit, text

*KitProducts*
KPID, autonumber
KitID, long integer
PID, long integer

*Books*
BookID, autonumber

*BookKits*
BKitID, autonumber
BookID, long integer
KitID, long integer


Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day ;)

remote programming and training
strive4peace2006 at yahoo.com

*

Kass wrote:


Crystal,

I REALLY appreciate you sending this info. You are right, this clears up a lot of issues and jargon with SQL. I think you just have to work with this stuff for a bit to start grasping how it all works together. I'm SLOWLY getting there.... but at least I can feel progress none the less.

I have the database about where I want it. I have one more hurdle. I have the Products with their product IDs (PID)... I have the Activities with their activity IDs... I have the Kits with their IDs (KitID). The Kit is an Activity and its ID with all the Products it takes to perform that Activity. So KitID 1 may have Activity 24 which includes Products 4, 8, 10, and 22. I also have the Books with thier book IDs (BID) and the Book Kits (BKitID). Each Book Kit has an individual BKitID number and name and takes a Book ID and seeds all the KitIDs and AIDs and Activity Names that go into making that Book. So a BKitID will have with it a KitID and all its associated AIDs+PIDs for each Activity included in the Book. All of this is now working great. I can seed and lookup Activities and all the Products that make up the individual Activities. I can also look up the Books and all the Activities that make up that book.

Now the next step. To somehow get in a report....
If we look up a Book, it tells us the Activities that are included in that book and all the Products that go into making up those Activities that make up that Book. I think I'll have to try to get a query that will take the BKitIDs and the KitIDs and bring that information together. If you have any great ideas, let me know!

Anyway, I really appreciate all your help... I love the info on SQL!

Thanks again!

Kass

"strive4peace" <"strive4peace2006 at yaho" wrote:




Hi Kass,

here you are!

Don't let the SQL acronym intimidate you, it is not tough to learn the basics.

~~~~~~~~~~~ SQL background ~~~~~~~~~~~

SQL is Structured Query Language

It is a statement to get data out of one or more tables/queries. This is what Access stores for:

1. Queries (just shows the QBE grid for convenience --
choose View, SQL)

2. RowSource for Comboboxes and Listboxes (if list does not
come directly from a table since a query is actually an SQL statement)

3. RecordSource for Reports and Forms (if list does not come
directly from a table)

Queries can be different types, for instance:
1. Select
2. Crosstab
3. Make-Table
4. Update
5. Append
6. Delete

Select and Crosstab queries show data while the others are "action" queries" and do not display data -- so you cannot use them for record sources or for row sources.

To expand and reiterate:

Queries are a very powerful tool in Access -- you can do far more than simply use them to select data

.... Queries can be ACTION Queries...they DO things, not show you things

they can add data to a table --> APPEND Query

they can make changes to a table --> UPDATE Query

they can delete records from a table --> Delete Query

they can make tables --> Make-Table Query

they can transpose data --> Crosstab Query (which is also, actually, SELECTing data from a table since there is not action)

Internally, Access stores an SQL (Structured Query Language) statement for queries*, not the visual representation you see when you design queries -- the QBE (Query-By-Example) grid is for your benefit, so you can use drag-n-drop and pick methods and visually look at things better.

*this is why it is so important to get comfortable with lokking at SQL statements

A great way to become more familar with SQL is to LOOK at the SQL everytime you make a query. Until you actually start to look, you never realize how easy and sensible it actually is... and It really helps to use Aliases (short abbreviations) for tablenames as it makes the SQL statement shorter.

~~~~~~~~~~~ SELECT ~~~~~~~~~~~

BASIC SQL SYNTAX

SELECT fieldlist

FROM tablename


IN anotherdatabase.mdb
WHERE conditions
GROUP BY fieldlist
HAVING conditions for fields that are grouped
ORDER BY fieldlist;

~~~~~~~~~~~ APPEND ~~~~~~~~~~~

An Append Query is a select query preceeded by

INSERT INTO Tablename (field1, field2, etc )

~~~~~~~~~~~ UPDATE ~~~~~~~~~~~
An Update Query first identifies the tables that are used

UPDATE table1 INNER JOIN table2 ON table1.keyfield =
table2.keyfield

Then identifies what to change

SET table1.fieldtochange = expression

Then, if you have criteria...

WHERE table.strField = 'somevalue'
AND table.numField = 99
AND table.dateField = #1/1/06#

~~~~~~~~ MAKE TABLE ~~~~~~~~

SELECT fieldlist
INTO tablename
IN c:\path\anotherdatabase.mdb

FROM tablename


WHERE conditions to process before recordset is made
GROUP BY fieldlist
HAVING conditions for fields that are grouped
ORDER BY fieldlist;

~~~~~~~~~~~ DELETE ~~~~~~~~~~~

DELETE A.*

FROM tblArticles AS A


WHERE (A.somefield=2);

~~~~~~~~~~~ CROSSTAB ~~~~~~~~~~~

TRANSFORM Count(B.Fieldname1) AS FieldAlias
.