query? access97

[micfly]

Still confused - beginner



Trying to build a query to show total "New Retail" sales.



Sounds simple but I can't figure it out. The records are stored in the
table: TblTracking.



The "New Retail" is one option selected from a combo box in the main form
where all the sales data is entered. New Retail is stored in the "Tracking"
field in the TblTracking table. It's pulled from another table in the combo
box so New Retail is stored as the ID# e.g. New Retail is "1" in the
Transaction field in TblTracking. (Sorry for the long wind).



I tried this (sql view):



SELECT TblTracking.Transaction, TblTracking.DealNumberID

FROM TblTracking

GROUP BY TblTracking.Transaction, TblTracking.DealNumberID

HAVING (((TblTracking.Transaction)="1"));



That gives me the deal records when I when I open the query but I want the
total of the deals. Adding SUM doesn't work?

How do I do this? Thanks!

 

[John W. Vinson]

Sounds simple but I can't figure it out. The records are stored in the
table: TblTracking.



The "New Retail" is one option selected from a combo box in the main form
where all the sales data is entered. New Retail is stored in the "Tracking"
field in the TblTracking table. It's pulled from another table in the combo
box so New Retail is stored as the ID# e.g. New Retail is "1" in the
Transaction field in TblTracking. (Sorry for the long wind).



I tried this (sql view):



SELECT TblTracking.Transaction, TblTracking.DealNumberID

FROM TblTracking

GROUP BY TblTracking.Transaction, TblTracking.DealNumberID

HAVING (((TblTracking.Transaction)="1"));



That gives me the deal records when I when I open the query but I want the
total of the deals. Adding SUM doesn't work?

How do I do this? Thanks!

What are the fields and datatypes in tblTransaction? You're showing only the
DealNumberID (which I would guess is the primary key of tblTracking, right?)
and the Transaction (which would be 1, not "1", if it's a Long Integer ID);
you don't post anything that makes sense to sum.

What are the fields and datatypes in tblTransaction? Your query would be
*something like* (bear in mind I can't see your table)

SELECT Sum(TblTracking.AmountField)

FROM TblTracking

WHERE (((TblTracking.Transaction)=1));

The WHERE clause will select only those records with Transaction equal to 1,
*before* doing the summing - HAVING sums them all and *then* filters the
results. And since you (apparently) only want a grand total sum, you don't
need to do any GROUP BY.

 

[Mickey Flynn]

Thanks for helping. All I want to count are the records with a 1 in the
Transaction field in TblTracking ( You are correct about the 1 not "1".)
and then give me the total number of those deals. So, If 14 deals have a 1
in the Transaction field in TblTracking I want it to calculate 14. The one
is the primary key (auto#) in TblTransaction which is the first record being
New Retail.There are a total of four records in TblTransaction: The second
record #2 is Used Retail and so forth. This allows the user to choose new or
used, etc. in the main form. I will need to build four queries one for each
total I need. Hope that makes sense.

 

[John W. Vinson]

Thanks for helping. All I want to count are the records with a 1 in the
Transaction field in TblTracking ( You are correct about the 1 not "1".)
and then give me the total number of those deals. So, If 14 deals have a 1
in the Transaction field in TblTracking I want it to calculate 14. The one
is the primary key (auto#) in TblTransaction which is the first record being
New Retail.There are a total of four records in TblTransaction: The second
record #2 is Used Retail and so forth. This allows the user to choose new or
used, etc. in the main form. I will need to build four queries one for each
total I need. Hope that makes sense.

You don't need four queries to get the four sums, unless you have some reason
to do them separately!

Create a new Query joining tblTracking to tblTransaction.
Select only the primary key of tblTracking and the text field from
tblTransaction (the field with "New Retail" in it).

Make it a Totals query by clicking the Greek Sigma icon.

Change the default "Group By" under the tblTracking field to "Count". Leave it
as Group By under the transaction name.

Open the query - you should see four rows, with the name of the transaction
and the count of records for that transaction.

You can Sum also - but not if you want to count; counting and summing are two
different mathematical operations!

 

[micfly]

Thanks for helping. Well that made a lot of sense. I built it as per your
instructions but it's not working.
When I click View the query it only shows the column names
CountOfDealNumberID and Transaction, nothing below that and the record
selector arrows at the bottom are grayed out. Here is the sql:

SELECT Count(TblTracking.DealNumberID) AS CountOfDealNumberID,
TblTransaction.Transaction
FROM TblTracking INNER JOIN TblTransaction ON TblTracking.DealNumberID =
TblTransaction.ID
GROUP BY TblTransaction.Transaction;

In the mean time I finally got one working as follows:

SELECT Sum(TblTracking.Count) AS SumOfCount
FROM TblTracking
GROUP BY TblTracking.Transaction
HAVING (((TblTracking.Transaction)="1"));

Which gives me the total I was looking for. What's wrong? FYI: this is
access97

 

[John W. Vinson]

Thanks for helping. Well that made a lot of sense. I built it as per your
instructions but it's not working.
When I click View the query it only shows the column names
CountOfDealNumberID and Transaction, nothing below that and the record
selector arrows at the bottom are grayed out. Here is the sql:

SELECT Count(TblTracking.DealNumberID) AS CountOfDealNumberID,
TblTransaction.Transaction
FROM TblTracking INNER JOIN TblTransaction ON TblTracking.DealNumberID =
TblTransaction.ID
GROUP BY TblTransaction.Transaction;

In the mean time I finally got one working as follows:

SELECT Sum(TblTracking.Count) AS SumOfCount
FROM TblTracking
GROUP BY TblTracking.Transaction
HAVING (((TblTracking.Transaction)="1"));

Which gives me the total I was looking for. What's wrong? FYI: this is
access97

What's wrong with my advice is that I have no idea how your table is
structured or what it's counting. You have a field named Count which has not
been mentioned in this thread, and which apparently contains a (stored?) count
of something. Given that, Sum is in fact appropriate.

If you're just trying to sum the value of Count for those records where
Transaction is equal to 1, you don't need to Group By transaction. Instead you
can use

SELECT Sum([tblTracking].[Count]) AS SumOfCount
FROM tblTracking
WHERE tblTracking.Transaction = 1;

Note that I didn't put quotes around the 1 since quotes are only needed for
Text fields.

I'd recommend changing the name of the field Count, since that's a reserved
word. Access will likely get confused if you try to do something like

SELECT Count(Count) From...

 

[micfly]

Didn't mean it that way, I don't know how you do it either without seeing
the tables but you're still a lot better than me. Sorry for the Count deal.
That was just a text field with a default value of =1 so every record has a
1 in this field in TblTracking. You can see where I am getting off the
beating track here as I should have been using the ID# as you suggested
first and was wondering why I couldn't get that to work. On your last
suggestion, using the Count field, this is one worked:

SELECT Sum(TblTracking.Count) AS SumOfCount
FROM TblTracking
HAVING (((TblTracking.Transaction)="1"));

I'll change the Count name as you suggested but, in the mean time, if you
would be so kind, maybe back to the:

SELECT Count(TblTracking.DealNumberID) AS CountOfDealNumberID,
TblTransaction.Transaction
FROM TblTracking INNER JOIN TblTransaction ON TblTracking.DealNumberID =
TblTransaction.ID
GROUP BY TblTransaction.Transaction;

Why do you think this isn't working? The TblTransaction table looks like
this:

Field Name Data Type
------------ ----------
ID AutoNumber
Transaction Text

When I open the table:

ID Transaction
--- ------------
1 New Retail
2 Used Retail
3 New Lease
4 Product only

Again, thanks for sticking with me.

Thanks for helping. Well that made a lot of sense. I built it as per your
instructions but it's not working.
When I click View the query it only shows the column names
CountOfDealNumberID and Transaction, nothing below that and the record
selector arrows at the bottom are grayed out. Here is the sql:

SELECT Count(TblTracking.DealNumberID) AS CountOfDealNumberID,
TblTransaction.Transaction
FROM TblTracking INNER JOIN TblTransaction ON TblTracking.DealNumberID =
TblTransaction.ID
GROUP BY TblTransaction.Transaction;

In the mean time I finally got one working as follows:

SELECT Sum(TblTracking.Count) AS SumOfCount
FROM TblTracking
GROUP BY TblTracking.Transaction
HAVING (((TblTracking.Transaction)="1"));

Which gives me the total I was looking for. What's wrong? FYI: this is
access97

What's wrong with my advice is that I have no idea how your table is
structured or what it's counting. You have a field named Count which has not
been mentioned in this thread, and which apparently contains a (stored?) count
of something. Given that, Sum is in fact appropriate.

If you're just trying to sum the value of Count for those records where
Transaction is equal to 1, you don't need to Group By transaction. Instead you
can use

SELECT Sum([tblTracking].[Count]) AS SumOfCount
FROM tblTracking
WHERE tblTracking.Transaction = 1;

Note that I didn't put quotes around the 1 since quotes are only needed for
Text fields.

I'd recommend changing the name of the field Count, since that's a reserved
word. Access will likely get confused if you try to do something like

SELECT Count(Count) From...

 

[John W. Vinson]

Didn't mean it that way, I don't know how you do it either without seeing
the tables but you're still a lot better than me. Sorry for the Count deal.
That was just a text field with a default value of =1 so every record has a
1 in this field in TblTracking. You can see where I am getting off the
beating track here as I should have been using the ID# as you suggested
first and was wondering why I couldn't get that to work. On your last
suggestion, using the Count field, this is one worked:

That field *should simply not exist*. It contains no information that doesn't
already exist in the table, i.e. the very existance of a record. If you want
to count records, you can count records using the Count function in a totals
query; you do not need a field like this to do so!

SELECT Sum(TblTracking.Count) AS SumOfCount
FROM TblTracking
HAVING (((TblTracking.Transaction)="1"));

I'll change the Count name as you suggested but, in the mean time, if you
would be so kind, maybe back to the:

SELECT Count(TblTracking.DealNumberID) AS CountOfDealNumberID,
TblTransaction.Transaction
FROM TblTracking INNER JOIN TblTransaction ON TblTracking.DealNumberID =
TblTransaction.ID
GROUP BY TblTransaction.Transaction;

Why do you think this isn't working? The TblTransaction table looks like
this:

Field Name Data Type
------------ ----------
ID AutoNumber
Transaction Text

When I open the table:

ID Transaction
--- ------------
1 New Retail
2 Used Retail
3 New Lease
4 Product only

Again, thanks for sticking with me.

But you have a *TEXT* field in tblTracking, named Transaction, with values
"1", "2" and so on!? Why? You can't link a Text field to an Autonumber field.
Is this perhaps a Lookup Field in which you *see* the text "New Retail" but
the table actually contains a concealed long integer 1? If so see
http://www.mvps.org/access/lookupfields.htm
for a critique of what many of us consider a misfeature.

Guessing that this is in fact the case... change the query to

SELECT TblTransaction.Transaction, Count(*) As CountOfRecords
FROM tblTracking INNER JOIN tblTransaction
ON tblTracking.Transaction = tblTransaction.ID
GROUP BY TblTransaction.Transaction;

Let's break this down so you can see what's going on.

First, I'm suggesting that you include both the tables in the query, Joining
them by connecting the TRANSACTION field in tblTracking (which, again, *I am
guessing* is a Long Integer foreign key created by the Lookup Wizard) to the
numeric ID field in Transaction. This Query will let you see any field in
tblTracking and any field in tblTransaction - in this case the only field we
care about is the text Transaction.

I'm making it a Totals query by including a GROUP BY clause. This is putting
all the records with "New Retail" in one group, all the records with "Used
Retail" in another group, and so on.

I'm including the count of all records - Count(*) - to count how many records
there are in each group.


Try copying and pasting this SQL into the SQL view of a new query, and then
going back to the query design window to see how you would set this up using
that window.

 

[micfly]

You're very patient, I appreciate it, but...you went over my head.

Perhaps these image files (hope I am not out of line here) of my
form/tables/queries will help you see what the heck

I'm doing. The Transaction field is a combo box in the form where the user
selects New Retail, etc.

I can't make your query work as I am not very good at deciphering sql. I
tried quite a few scenarios but can't figure it out.

Thanks again for any help, this is driving me crazy!



http://home.alltel.net/mcfly/tmp/FrmTracking.jpg

http://home.alltel.net/mcfly/tmp/FrmTrackingEdit.jpg

http://home.alltel.net/mcfly/tmp/TablesDesign.jpg

http://home.alltel.net/mcfly/tmp/TablesOpen.jpg

http://home.alltel.net/mcfly/tmp/MyQuery.jpg

http://home.alltel.net/mcfly/tmp/YourQuery.jpg

 

[John W. Vinson]

You're very patient, I appreciate it, but...you went over my head.

Perhaps these image files (hope I am not out of line here) of my
form/tables/queries will help you see what the heck

I'm doing. The Transaction field is a combo box in the form where the user
selects New Retail, etc.

Stop.

Data is NOT STORED IN FORMS. It's stored in tables, and only in tables.

Data is NOT STORED IN COMBO BOXES. Fields are not combo boxes. A combo box *is
a tool*, a way to manage data stored in a table.

A Combo Box has a "Row Source", a Query (usually) from which it gets its data.

I can't make your query work as I am not very good at deciphering sql. I
tried quite a few scenarios but can't figure it out.

Thanks again for any help, this is driving me crazy!



http://home.alltel.net/mcfly/tmp/FrmTracking.jpg

http://home.alltel.net/mcfly/tmp/FrmTrackingEdit.jpg

Well, those are of no help since they conceal the actual structure of your
data.

http://home.alltel.net/mcfly/tmp/TablesDesign.jpg

That's a little better... when you select the Lookup tab on the bottom of the
screen when Transaction is highlighted, what do you see?

http://home.alltel.net/mcfly/tmp/TablesOpen.jpg

And this is helpful. It means that the Transaction field *IS A NUMBER*, not a
text string.

http://home.alltel.net/mcfly/tmp/MyQuery.jpg

I do not understand why you are *still* using "1" rather than 1 as a
criterion!!! Have you even tried following my advice? There's no evidence of
it so far.

http://home.alltel.net/mcfly/tmp/YourQuery.jpg

And what happens when you try to open that query?

 

[micfly]

How do I do what I am trying to do then? That was the only way I could
figure out how to give them the selection of New Retail and so forth. It is
being stored in the table, right??? Very confused now :-(

 

[John W. Vinson]

How do I do what I am trying to do then? That was the only way I could
figure out how to give them the selection of New Retail and so forth. It is
being stored in the table, right??? Very confused now :-(

Perhaps you could explain what you ARE trying to do. So far I've seen one
example, a count of all records in the table with New Retail. You're getting
that count.

What *do* you want?

 

[micfly]

Well, I eventually will need to print a report showing total sales for New
Retail, Used and so forth.
But if my tables are not set correctly I need to stop and try and get this
structured right before I get too far.
I don't know anything about Access, I just jumped in here and started
building this. My question now is: Is the combo box box the wrong way to
select New Retail? Thanks again for your patience!

 

[John W. Vinson]

Well, I eventually will need to print a report showing total sales for New
Retail, Used and so forth.

In that case you do not need four queries, you need *only one totals query*.

That query - which I posted several messages back! - can give you four lines,
each for a category, showing the total for that category.

It is NOT necessary to store a total in a table to produce a report - in fact
it's a very bad idea. Instead you would create a query to calculate the
totals, and base your report on that query.

But if my tables are not set correctly I need to stop and try and get this
structured right before I get too far.

I think your tables are correct - I'm not sure of it, since I still don't know
(because you haven't said) what is the datatype and Lookup status of the
Transaction field in your table. But please try this: create a new Query.
Don't select any tables. Select View... SQL from the menu, and copy and paste
this into that window (overwriting the SELECT; that should be there):

SELECT tblTransaction.Transaction, Count(*) FROM tblTracking INNER JOIN
tblTransaction ON tblTransaction.ID = tblTracking.Transaction GROUP BY
tblTransaction.Transaction;

Save the query as qryTransactionCount.

Open it. What do you see?

Create a new Report. When asked "Table or Query" choose Query and select
qryTransactionCount. Let the report wizard build a report for you.

Does it give you what you want?

I don't know anything about Access, I just jumped in here and started
building this. My question now is: Is the combo box box the wrong way to
select New Retail? Thanks again for your patience!

My point is that to accomplish what you say you want to accomplish - a report
listing counts of each type of transaction - you *simply don't NEED* to select
New Retail. Sure, you can use a combo box on a form to do so, in some other
operation where you do need to select it! But in this case - unless I'm
completely misunderstanding your goal - you DON'T.

If you DO need to select a particular transaction, please post back and
explain. It's not hard to do but I hate to have you go through the work to do
so if it's not needed.

 

[micfly]

I'm sure my inexperience gets lost in the translation. I am just now to the
point where I know I need queries and just have happened to start with this
one. I'm not sure if I'll need four queries or just one showing all four
totals depending on how my report layout ends up. I was trying to get your
query to work but I am getting "Type mismatch in JOIN expression" error. The
Transaction field in TblTransaction is a textbox (I think that is what you
are asking for?). I had some image links to help you see (rather than me
fumbling with incoherent descriptions) maybe this is a little better format:
http://home.alltel.net/mcfly/tmp/DB1_pics.htm Also, I was not trying to
store the totals that is why I need help with the queries.

 

[John W. Vinson]

I'm sure my inexperience gets lost in the translation. I am just now to the
point where I know I need queries and just have happened to start with this
one. I'm not sure if I'll need four queries or just one showing all four
totals depending on how my report layout ends up. I was trying to get your
query to work but I am getting "Type mismatch in JOIN expression" error. The
Transaction field in TblTransaction is a textbox (I think that is what you
are asking for?).

No. The Transaction field is NOT a "textbox". A textbox is a type of "control"
on a form or report. Tables do not contain textboxes! They contain Fields,
which can be of various datatypes - Number, Date/Time, Text, and so on. You
can tell which type it is by opening the table in design view and selecting
the field, and looking in the second column.

Did you use a wizard to create the table? Or create it manually?

I had some image links to help you see (rather than me
fumbling with incoherent descriptions) maybe this is a little better format:
http://home.alltel.net/mcfly/tmp/DB1_pics.htm Also, I was not trying to
store the totals that is why I need help with the queries.

Ok... Make a backup of your database. Open tblTracking in design view. Select
the Transaction field (which contains numbers, 1 and 2 currently it seems).
Change its Datatype from "Text" to "Number"; in the box at the bottom of the
screen be sure that it's "Long Integer" in the first row, and remove the 0
that Microsoft all-too-helpfully but erroneously puts in the Default Value
row. Save the table.

Now click the "Relationships" icon on the toolbar (looks like three little
datasheets connected by lines). Add tblTracking and tblTransactions. Drag the
ID field from tblTransactions to the Transactions field in tblTracking - this
will create a "Relationship" between the tables to ensure that you can only
put a valid number into the field in tblTracking.

NOW try the query again... we'll get this working yet!

If you haven't already done so, you might want to read one of the tutorials on
one of these websites:

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

A free tutorial written by Crystal (MS Access MVP):
http://allenbrowne.com/casu-22.html

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials

 

[micfly]

The data type is text. The table is in the pics here:
http://home.alltel.net/mcfly/tmp/DB1_pics.htm
I created the tables manually.

 

[John W. Vinson]

The data type is text. The table is in the pics here:
http://home.alltel.net/mcfly/tmp/DB1_pics.htm
I created the tables manually.

The problem then is that you're trying to link a Text field (in tblTracking)
to a Number (Autonumber) field in tblTransactions.

Follow my suggestions in the previous message and post back with the results.

 

[micfly]

You are the man! I'll tell you one thing; you have the patience of Job! Now
it's working...thanks for that. Very nice query, hopefully it will help with
the others I will need to build. Yeah, I've tried to read as much as I can
but I'm still in the early learning stages as you can tell, so it's taking a
while to absorb all this. This one was a big learning experience, though,
many thanks to you

 

[John W. Vinson]

You are the man! I'll tell you one thing; you have the patience of Job! Now
it's working...thanks for that. Very nice query, hopefully it will help with
the others I will need to build. Yeah, I've tried to read as much as I can
but I'm still in the early learning stages as you can tell, so it's taking a
while to absorb all this. This one was a big learning experience, though,
many thanks to you

You're most welcome! Glad you got it working. And have a merry Christmas!