Re: Populating a list -- table structure?

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
SELECT
A.Fieldname2,
A.Fieldname3

FROM Table2 AS B

INNER JOIN Table1 AS A
ON B.someID = A.someID
GROUP BY
A.Fieldname2,
A.Fieldname3
PIVOT By B.Fieldname1;

you can use equations to pivot (this will be column headings). For instance, if you want the column headings to be year and month, you can do this:

PIVOT Format([DateField],'yy-mm');

If you also want a column that totals whatever you have in all the VALUE columns (the expression after TRANSFORM), repeat your transform expression after the SELECT keyword and the GROUPBY keywords

for instance:

TRANSFORM Count(B.Fieldname1) AS FieldAlias
SELECT
A.Fieldname2,
A.Fieldname,
Count(B.Fieldname1) AS FieldAlias2

FROM Table2 AS B

INNER JOIN Table1 AS A
ON B.someID = A.someID
GROUP BY
A.Fieldname2,
A.Fieldname3,
Count(B.Fieldname1)
PIVOT By B.Fieldname1;

~~~~~~~~ ALIASES ~~~~~~~~

Using Aliases for tablenames (/querynames) makes the SQL easier to read.

For calculated fields, it is best to assign your own field alias instead of letting Access use "expr1", "expr2", etc. Calculated fields MUST have aliases.

an Alias follows the keyword AS

The SQL statement can be selected, copied, then pasted into Word for formatting and printing (makes great wallpaper for your wall, not your computer ;) as you are learning) or into Notepad to have as a popup reference while you are working into design view, etc.

~~~~~~~~ JOINs ~~~~~~~~

When you are getting information from more than one table, the default join type is INNER JOIN. This means that only records in common will be displayed. For instance, if you have a table of Companies and a table of Jobs and not every Company has done a job, but you want to see ALL the companies anyway, you would use a Left Join or a Right Join for the type of relationship between tables.


FROM Companies AS c

LEFT JOIN Jobs AS J ON c.CompID = J.CompID

The join is specified as a LEFT JOIN because ...
ON c.CompID = J.CompID --> C is on the LEFT side of the equal sign.

~~~~~~~~ PARAMETERS ~~~~~~~~

If you specify data type in Query, Parameters, the SQL statement is preceeded by (for example):

PARAMETERS [enter category] Text ( 50 );

while the criteria may be:

WHERE (B.Category=[enter category])

~~~~~~~~ LOOK at SQL ~~~~~~~~

Whenever you create a query, LOOK at the SQL statement and study it for a couple minutes -- it makes sense! Within a short period, the confusion will be gone...


from the menu, choose:
.

Relevant Pages

  • Re: Populating a list -- table structure?
    ... I had made a report already and figured out about adding the ... your responce below, but thanks to your help with SQL, I was able to get the ... It takes a summary from a select query and gives the ... KitID, long integer ...
    (microsoft.public.access.forms)
  • Re: Official Status of SQLServer 2005 ADP
    ... I have said that the support for SQL passthrough ... queries under MDB was bad and worst than the one offered by ADP while you ... > attempt to "pass through" every Access query against a linked ODBC ...
    (microsoft.public.access.adp.sqlserver)
  • Re: "Query Too Complex" Errors
    ... few dozens of queries, in the middle of which there's a long chain of ... we've been having a lot of those "Query Too ... some of the complexity in the SQL ... SQL statement you are working on. ...
    (microsoft.public.access.forms)
  • Re: Dynamic query problem
    ... On Oct 17, 9:41 am, Andy Hull ... If we were to provide a fully featured dynamic query generator we would have ... Provided with already built queries which they can edit ... SQL and see where it is the same as for the other queries and where it ...
    (microsoft.public.access.queries)
  • 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)