Re: Populating a list -- table structure?

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
.

Relevant Pages

  • Re: Populating a list -- table structure?
    ... 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, ... Queries (just shows the QBE grid for convenience -- ...
    (microsoft.public.access.forms)
  • Re: Populating a list -- learning Access
    ... It is kinda funny -- I started using databases in the early 80's with dBase and, for years, never knew that I knew SQL! ... 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. ... 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. ... Queries (just shows the QBE grid for convenience -- ...
    (microsoft.public.access.forms)
  • Re: Using SQL statement to append a recordset to a table
    ... To insert a value into the field Priority: ... > all failed mean while each time my report was working fine. ... Used the query> wizard "Find Unmatched Query Wizard" to create a query between these two> tables, changed to an append query, appending to the ... > table (looking at the sql view along the way). ...
    (microsoft.public.access.formscoding)
  • Re: Creating Access DB
    ... database and in the SQL database that your report needs. ... click the query tab. ...
    (microsoft.public.excel.programming)
  • Re: Populating a list -- table structure?
    ... It takes a summary from a select query and gives the ... KitID, long integer ... lot of issues and jargon with SQL. ... The SQL statement can be selected, copied, then pasted into ...
    (microsoft.public.access.forms)