UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Make A Table From Fields On A Form, Access 2016    
 
   
WeissSchnee
post Mar 20 2017, 11:57 AM
Post#1



Posts: 6
Joined: 20-March 17



Hello,

I wasnt sure how to word this right. I am NOT trying to make an Access Object table.

I am trying to create a 5x5 table in a form similar to what I created below.

**************

Year 1 ______________ Year 2_________________ Year 3 Class ______________


Year 1 Year 2 Year 3 Total
Asian
Hispanic
White
Total

*************
This table runs of a query I made. And the “Start Date, End Date, and Class” fields are the criteria linked to my query. I am able to get the total rows and columns without any difficult by using the method below for each class.

=DCount("[Class]","qryUniversity","[Class] = 'Science'")
=DCount("[Class]","qryUniversity ","[Class] = 'Math'")
=DCount("[Class]","qryUniversity ","[Class] = 'Biology'")
=DCount("[Class]","qryUniversity”)

I input this code into the control source of the fields in my form.

I am having difficulty with everything else. First of all, is there a way I could the making my Query know that when I enter “2016” under txtYearOne, I actually mean “>=1-1-16 and <=12-31-16”?

Secondly, how can I get DCount to count values based on TWO criteria?

Would I do something like this:
=DCount("[Class]","qryUniversity","[Class] = 'Science' “[txtYearOne] = “2016” ")
(Blue refers to my query and red refers to the criteria of my query)
Go to the top of the page
 
theDBguy
post Mar 20 2017, 12:26 PM
Post#2


Access Wiki and Forums Moderator
Posts: 69,685
Joined: 19-June 07
From: SunnySandyEggo


Hi,

Welcome to UtterAccess!
welcome2UA.gif

Just to make sure you're not working harder than you need to, have you tried using a Crosstab query yet?

--------------------
Go to the top of the page
 
WeissSchnee
post Mar 20 2017, 01:28 PM
Post#3



Posts: 6
Joined: 20-March 17



I appreciate the welcome, and the tip!

And no. I thought you could only use a CrossTab query for making graphs.

Basically, I have a query that shows me the number of Asian students in a certain class. I then have this broken into 3 years.

If there was a way I could have a query display this exactly how I want, that would be great.
Go to the top of the page
 
WeissSchnee
post Mar 20 2017, 01:43 PM
Post#4



Posts: 6
Joined: 20-March 17



Also, do you know how I can get Access to understand that when I enter "2016" into a field on a form, I actually mean #1-1-16 to 12-31-16#
Go to the top of the page
 
theDBguy
post Mar 20 2017, 01:57 PM
Post#5


Access Wiki and Forums Moderator
Posts: 69,685
Joined: 19-June 07
From: SunnySandyEggo


Hi,

It might help us understand what you need if you could post a small copy of your db with test data. A crosstab can be used to tally the data by grouping them. The result doesn't have to be used for graphs.

To enter only 2016, use a numeric field rather than date/time. Then, in your query, either construct the full year or extract the year from the dates.

Hope it helps...

--------------------
Go to the top of the page
 
WeissSchnee
post Mar 20 2017, 02:06 PM
Post#6



Posts: 6
Joined: 20-March 17



Before I upload an example of my database, can you tell me how I can " in my query, either construct the full year"
Go to the top of the page
 
theDBguy
post Mar 20 2017, 02:12 PM
Post#7


Access Wiki and Forums Moderator
Posts: 69,685
Joined: 19-June 07
From: SunnySandyEggo


Hi,

One way is to use the DateSerial() function.

Hope it helps...

--------------------
Go to the top of the page
 
WeissSchnee
post Mar 20 2017, 02:29 PM
Post#8



Posts: 6
Joined: 20-March 17



I do not know how to use that function in a query... Here is a sample


This post has been edited by WeissSchnee: Mar 20 2017, 02:32 PM
Go to the top of the page
 
WeissSchnee
post Mar 20 2017, 02:32 PM
Post#9



Posts: 6
Joined: 20-March 17



zip
Attached File(s)
Attached File  Files.zip ( 44.99K )Number of downloads: 2
 
Go to the top of the page
 
ScottGem
post Mar 20 2017, 02:46 PM
Post#10


UtterAccess VIP / UA Clown
Posts: 32,050
Joined: 21-January 04
From: LI, NY


Your attachment makes no sense

You have 2 tables. One with a list class and races for the class and another with a list of dates. There is no relationship between them.

You should have at least three tables:

tblClasses
ClassID (PK Autonumber)
Class

tblRaces
RaceID (PK Autonumber)
Race

tblClassRace
ClassRaceID (PK Autonumber)
ClassID (FK)
RaceID (FK)
ClassDate

From there you can create a crosstab query.


--------------------
Scott <>
Scottgem's Blog
Microsoft Access MVP since 2007
Author: Microsoft Office Access 2007 VBA
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    27th May 2017 - 11:49 PM