Simply Count Query... Why So Difficult?
Jun 16, 2006
I have Two fields with DateWorked and EmployeeNumber. All I want to do is count the number of employees that worked on a specific date. In the employee numbers I have 4 dummy numbers that I don't want to count in my query (00001-00004), but will have entries almost each day. The rest are 5 digit numbers.
Some employee will work some days, but not others.
So I set up my query with:
EmployeeNumber
Table
Total: Count
Criteria: >4
I did that think that it would count all employee numbers great than four, but all it seems to do is give me the count of days that more than 4 employees worked.
What am I doing wrong? I feel like this is simple, but after some of the more complex things I've done over the past week, this seemingly "easy" thing has me stumped.
View Replies
ADVERTISEMENT
Jan 17, 2007
Alright people... I'm ashamed to even ask but here goes.
I have two tables:
1) Employees
2) Neighborhoods
Table Employees has a comprehensive list of all employees and their information (including phone numbers).
Table Neighborhoods has lots of information about each neighborhood we're working in and it also specifies what people play certain roles in each neighborhood. For instance, there is a field for "Field Manager" and "Sales Consultant". These two fields are exact matches of "Full Name" from Table Employees.
All I want is for my Neighborhoods Table to be updated or a new table be created that imports the "Field Manger Phone Number" into the Neighborhoods Table and the same for the "Sales Consultant".
-----------------------------
To be explain one other way, this is what I want:
Table: Employees
Field: Full Name
Field: Phone Number
Table Neighborhoods
Field: Field Manager (already equals Full Name)
Field: Sales Consultant (already equals Full Name)
Field: Field Manager Phone Number (This is the field I want pulled from Table Employees)
Field: Sales Consultant Phone Number (This is the field I want pulled from Table Employees)
-------------------------------
I've tried my best to create an Update Query or a Make Table query but I have not gotten any good results. Can someone please help me out with this being very descriptive as I am certain I'm just goofing one simple step.
Thanks in advance.
View 4 Replies
View Related
Apr 15, 2008
Hi all,
I have 2 tables, namely: Projects and WeekUren
Where [WeekUren] contains multiple references to unique records in [Projects].
[WeekUren] has the following collums which are important:
-ProjID
-Week
-Persoon
Where these three always form an unique combination. For instance:
1 | 1 | 1
1 | 2 | 1
1 | 3 | 1
1 | 1 | 2
1 | 2 | 2
2 | 1 | 2
Saying: Person1 will work week 1 till 3 on Project1, Person2 will work week 1 and 2 on Project1 and week1 on Project2.
Weeks are here relative to the project start date (a value of [Projects]) and always start with and increment with 1. So week1 of Project 1 and 2 don't neccesarily fall on the same dates, hence Person2 is still able to work both Projects.
Bear with me here.....
Now I need to select all duplicate records (where people are assigned to two (or more) projects in the same real week.
Real week is defined by:
Code:DatePart("ww",((WeekUren.Week-1)*7+(SELECT Projects.Start FROM Projects WHERE Projects.Id=WeekUren.ProjId)))
And the Query to convert [WeekUren] in to the relative form is:
Code:SELECT WeekUren.Id, DatePart("ww",((WeekUren.Week-1)*7+(SELECT Projects.Start FROM Projects WHERE Projects.Id=WeekUren.ProjId))), WeekUren.Uren, WeekUren.Persoon, WeekUren.ProjIdFROM WeekUren;
The query to print out duplicates is ofcourse:
Code:SELECT *FROM WeekUrenWHERE (((WeekUren.Week) In (SELECT [Week] FROM [WeekUren] As Tmp GROUP BY [Week],[Persoon] HAVING Count(*)>1 And [Persoon] = [WeekUren].[Persoon])))ORDER BY WeekUren.Week, WeekUren.Persoon;
Sofar so good, combination of the two however give me a syntax error. Hence my question here how to construct such a diabolical Query. Thanks!
Regards,
Jacco
View 4 Replies
View Related
Mar 21, 2006
There should be a simple solution to this.
I have two fields with numbers in them ( dollars ).
I run a make-table query to add a third field that will show the sum of the two fields and my results in the third field are wrong.
Whenever there is a blank in the second field the third field shows a blank as well.
like this:
(Field 1 ) ( Field 2 ) (Field 3 = Field 1 + Field 2)
100.00 100.00 200.00
200.00 <blank> <blank>
130.00 100.00 230.00
my fields have the same data type and formatting so I don't understand where the problem is?
View 2 Replies
View Related
Aug 1, 2006
I have one table with 1Milllion records.
I have another table with same column headings with 260 thousand records.
Microsoft Access gives me an error when trying to append these two tables together into one big one.
How can I resolve this issue?
View 3 Replies
View Related
Jun 16, 2014
I'VE objects in ACC2003 as per following :
Form name : INVOICECreate
Query Name : db_InvoiceSet
Reports :
1st Report : Report_Inv1
2nd Report : Report_Inv2
3rd Report : Report_Inv3
All Reports based on Single db_InvoiceSet due to some cross and different manipulation of data for different office department
So, I need is:
Open All Reports in Print Preview Mode with Single Click of Button in Form INVOICECreate
View 2 Replies
View Related
May 7, 2007
I think i'm making this more difficult than it is.
I have an 2 excel spreadsheets with sales numbers like this -
Zip CustomerType Sec Hes Fol
10001S0303.230
10002C-373461.391531.8
10577S-71.25287.560
11211C0702.220
11803L14365.27-45
12401S0280.990
12524W0178.320
One file is numbers I averaged over the past 3 months. The other file is actual numbers. Not all zip codes are in each file. I need to figure out the difference between the 2 and add the difference to this months numbers. So I created a query and found there were 94 records that matched based on zip cod and customer type. Then I found the difference in sales amounts. Now I'm stuck at figuring out which zip code and customer type don't match in the 2 files. So if zip code 10001 with customer type S isn't in the actual data for this month I need to add the zip code and customer type and have it be -303.23.
View 1 Replies
View Related
Jul 24, 2006
Hey,
I’ve posted on here a few times lately, as I’m creating a database at work. It is going to be used to keep various information about new persons applying for jobs, and I want it be viewed/sorted by either 'Job Grade' or 'Job Title' I keep doing a lot, and then getting stuck on certain things. I’m stuck again, but I think it’s a little more difficult this time…
What I would like to happen is have the database open up, and a screen appears with two choices – ‘View by Job Title’ and ‘View by Job Grade’. When you click one, a new screen appears and a list of some sort appears with all of the job grades / job titles (depending on which button is pressed) on that are currently in the database.
From here, you can choose a job grade / title and it will bring up the form I have created – but only with the records that have the chosen job grade / title.
So for example, there could be 20 records in the table. 5 with job title ‘Manager’, 5 with job title ‘Assistant’. Then there could be 5 jobs with grade ’1’ and 5 jobs with grade ‘2’. When you clicked on ‘View by Job Title’ and then choose ‘Manager’, the 5 records with the job title ‘Manager’ would come up.
I hope I’m making sense so far.
The data would all be stored in one main table, and I already have the form created, so it would be good if I could use this.
Is there any way to do all of the above?
If anyone could help at all, it would be very much appreciated.
Cheers
Dan
View 3 Replies
View Related
Aug 21, 2006
I have a solution for my problem, I just want to see if there is an easier way. Here is my predicement:
From...........To..............c
0...............100............5
100............500............10
500...........1000...........15
1000.........3000............25
3000.........5000............30
5000........10000...........40
10000.......30000...........50
30000.......50000...........60
(This means: Between the amount of 0 and 100, the eventual amount equals to 5. Between 100 and 500, the evebtual amount is 10. etc. etc.)
Over the amount of 50,000 for every 5,000 the amount goes up, the eventual amount (column c) goes up 10, with the condition the eventual amount (column c) should not be over 500.
My current If-statement: Eventual Amount:
IIf([Amount]<100,5,IIf([Amount],500,10,IIf([Amount]<100,15 etc. etc.)))
Is there an easier way to do this... It's going to take mighty long to do it like I am currently doing it.
Thanks a lot
Regards
kruger101
View 6 Replies
View Related
Apr 12, 2006
I have a database for my company which I am making.
The products are hydraulic cranes and come in numerous models (or sizes), in turn these models come in numerous versions, 5 different ones in all, lets call them for simpleness 1-5. However, some cranes come in versions 1-3 only, others 4-5 only, some all versions and no-doubt some a random mix!
At present I have a table with the fields "Model" (Text), and seperate fields for each "Version" (ie. 1, 2, 3 etc), these are Yes/No tick boxes. Ticking a given box for any given model, means that only these options are available on a form. Ie When you select a "400" crane, you can for example only select Version numbers 1, 2 and 3 - those that are checked in this table.
What I need to do is when selecting each individual model on a particular order is to display 2 figures hydraulic oil flow-rate and also hydraulic pressure required from a reference table I assume. These figures are different for every given model/version combination.
The difficult part as I see is relating the yes/no check box from the table above to version number on the reference table.
Does this make any sense? My head hurts.
Any Help would be super!
View 2 Replies
View Related
Oct 17, 2005
How difficult is it to make a sychronisationfunction?
I have a meetings database. Sometimes the meetings are enterd localy and need to be sychronized with the main file. Is this hard to do? where do i start.
Thank you for your reply!
Joost
View 1 Replies
View Related
Dec 23, 2005
Hi Everybody. I've been nosing around here because I have this difficult problem with a database design. I modelled my program in UML so I have a class diagram. Now I want to create an access database out of it, but this is too hard for me.
It's about a school project for flowers. Every year, they make a cross plan. This cross plan contains crossings. Many crossings. And every crossing exist from 2 genotypes. A mother and a father. With this crossing several new genotypes are created. How on earth do I realize that in a database? A plant is male as well as female so you don't need to indicate which sex it is.
Further more I want a genotype to be judged on his characteristics by a user as much as he wants to.
Well...I hope someone can help and if you have questions about it don't hesitate to ask.
Thanks in advance!
Gr Billy
View 4 Replies
View Related
Nov 14, 2006
Hello,
My problem is this:
I want to retrieve some data from the database, the data is as follows:
pourcentage de processus évalués dans l’année
my problem is that when I am writing a querry for this...it gives me an error because of the << ' >> mark in the word << l'année >>
my querry is this...
strSQL = "insert into temp_indicateur (id,description) values(" & rs!id & ",'" & rs!description & "')
I am getting these values from another recordset.
Can anyone please help??
View 2 Replies
View Related
Dec 1, 2005
Hi all
I have a financial database that gets downloaded transactions off the internet from our accounts. The problem is that the transaction payer/payee is not always unique and needs to be classified. This is easy done using If statements but I really want to enter parts of the string into a table and have a query return a category for this payer/payee. Example:
I want this: AUTO ONE CLARKSON CLARKSON WA AU006495
to be recognised as: car parts
or with this tranaction:
CLARKSON MINI MART CLARKSON553908
look for "mini mart" and return supermarket
This step will make classifying transactions a lot simpler and user friendly.
thanks
View 3 Replies
View Related
Mar 31, 2008
Alright, so my pride and joy and departmental savior database has been fully implemented and people are so pleased with it that they *all* want to use it at the same time. Initially, I made it custom for three peoples' UI preferences, but since it's such an intuitive design, other staff now feel comfortable using it instead of passing the work down the line to the one person who knows how to use that "newfangled thing".
A couple years ago, I used FMPro and allowing for multiple users at once was an annoyingly complicated process dealing with permissions and rights and accounts and passwords and yadda yadda. Is it any easier to set up in Access? Does anything special even need to be done to allow for multiple simultaneous users?
View 3 Replies
View Related
May 24, 2005
Hi,
This is a toughie (i think so anyway!). I'll attempt to explain!!!
I start with a CONTINUOUS form in my DB that shows all readings for a single given customer's connections. E.g. Customer A has 5 connections and each connection has say 3 readings. Thus this query which uses joins between the Customers, Connections, and Readings tables, would return a list of all readings for each connection, so in the above example: 1*5*3 = 15 records.
Not too tricky so far, but then what I want is for each connection to have just one line showing the latest reading (easily achieved by use of SQL Aggregate Max function on the date field coming from the readings table) TOGETHER WITH the last-but-one (next most recent) reading. So back to the example taking Customer A's connection 1, the row would be as follows:
Cust Conn CurReading PrevReading
A 1 750 500
Where the CurReading value (750) comes from a different record than PrevReading (500).
I've tried all sorts of ways (subqueries etc.) to achieve this without success. The main problem being that any sub query would require parameters from the current record's fields, which seems not to be possible. Can anyone help or is this simply not possible in MSAccess Forms. If it isn't possible anyone have any suggestions as to an alternative way?
Thanks
Darren
View 2 Replies
View Related
Apr 2, 2013
I run a physical therapy office and patients come in for treatment either 3, 4 or 5 times per week. My database is used to track these frequencies (among other things).
I have 3 queries which count how many patients come in 5, 4 and 3 times/week.
In my main table I have fields called "how many 5's", "how many 4's" and "how many 3's".
I have tried to design an update query which will update those fileds in my main table to reflect the counts in the 3 queries mentioned above.
(I'm not using SQL view, I'm using the query design view)
In the "update to:" row, I use the Build function and locate the count I'm looking for.
Problem: when I run the query I get the error: Operation must use an updateable query.
View 3 Replies
View Related
Jul 26, 2006
Ok, hope you can help me with this one.
I have a select query that pulls data from one table that includes the following data
MOS Grade Required Authorized
I have a separate count query that pulls data from another table and counts the following data
MOS Grade Assigned
How do I combine these 2 queries so the results I get will be
MOS Grade Required Authorized Assigned
For instance:
Select Query Says
MOS Grade Required Assigned
25C E4 1 1
42A O3 1 1
38A E7 3 3
Count Query Says
MOS Grade Assigned
25C E4 1
42A O3 1
38A E7 2
I want the results to read
MOS Grade Required Authorized Assigned
25C E4 1 1 1
42A O3 1 1 1
38A E7 3 3 2
Hope you can help.
View 5 Replies
View Related
Jul 5, 2005
how can i count the number of rows in a query.
i have a query that return me number of rows. i want to count the rows.
when i try to count it, it gave me a column that count only her column. which mean in every row it counts me '1'.
i want to count all the rows.
View 4 Replies
View Related
Dec 9, 2005
I know this might sound stupid but I really can't find it anywhere. How do you count how many times a query is run.
I need to make a DoCmd.RunSQL stringname and count it so that I can loop it to match a certain number. Is that possible?
View 2 Replies
View Related
Dec 14, 2005
Hi everyone,
I wonder if someone could help me.
I am trying to write a query for a hospital database which counts the number of patients who received various treatments throughout their stay in intensive care. I think I will have trouble explaining textually what I would like to achieve so I have attached a Word document which should illustrate my requirements sufficiently.
If anyone could help me I would be extremely grateful.
Many thanks and best wishes
Russell
View 1 Replies
View Related
May 27, 2006
Ive got something like this table:
TourID Language ClientID
2000 German 213
2000 French 245
2000 Spanish 286
2100 German 289
2200 French 259
2200 Italian 246
etc...
Does anyone know how to count the specfic data in a field, I want to be able to count e.g. how many italian in tourID 2000? i have some idea of using the count function but how do u display zero??
Thanks in advance.
View 1 Replies
View Related
Aug 4, 2006
Hello everyone,
I have a table Deliveries with 4 columns;
CarNo (text), Name (text), DeliveryDate (number), ReceivingDate (number).
I need help with a query which summaries;
total number of deliveries,
deliveries on time (DeliveryDate = ReceivingDate )
late deliveries (DeliveryDate < ReceivingDate)
early deliveries (DeliveryDate > ReceivingDate)
So the out put should be like;
CarNoNameOnTimeLateEarlyTotal
3Taxi1053 18
So far I only managed to get the figures from 4 separate queries, and its really time consuming to collect all figures in one report.
Regards
Peter
View 2 Replies
View Related
Aug 17, 2006
I have a query that I want to get some totals on in a certain date range.
The fields in the query are:
SystemGroup
Problem
FaultCount
Date
Under Fault Count there is a possibility of two answers: Cosmetic or Mechanical
What I would like to do in the query is after I run the Query between two dates
I would like to see what my two totals are for Cosmetic Faults and Mechanical Faults.
I thought I had an expression that would work but it will not work. I am stumped.
So bottom line: after I run the query I would like to see:
Total Faults = ??
Cosmetic = ??
Mechanical = ??
Thanks in advance
View 1 Replies
View Related
Sep 29, 2006
I have setup a count query and only want it to count a number if it is not a 0.The count query seems to still count 0's.
How do i set the criteria so that 0's don't get counted?
View 2 Replies
View Related
Mar 1, 2007
I would like to know if anyone knows if I can create a query that will give me a count of the number of queries within my access DB?
Thank You
View 2 Replies
View Related
