Count City
Aug 23, 2006
I have a problem with counting the people in each city. I will give a discription of my tables first and than explain what I want.
City
ID
City
LocationID
People
ID
Name
BirthPlace
DeadPlace
...
What I want is a list of all the cities in LocationID = 1 and a count of all the people that where born there.
Example:
Bazel 12
Belsele 47
Beveren 170
Burcht 1
Daknam 0
De Klinge 9
Doel 1
Eksaarde 6
Elversele 3
Haasdonk 46
Kallo 1
Kemzeke 5
Kieldrecht 5
Kruibeke 13
Lokeren 10
Meerdonk 2
Melsele 13
Moerbeke 12
Nieuwkerken 35
Rupelmonde 2
Sinaai 49
Sint-Gillis 23
Sint-Niklaas 106
Sint-Pauwels 33
Steendorp 3
Stekene 45
Temse 16
Tielrode 3
Vrasene 5
Waasmunster 21
Zwijndrecht 10
My problem is that there are always a few cities missing and I can't seem to get my SQL correct.
Thanks
View Replies
ADVERTISEMENT
Aug 26, 2005
Does any one know of a place online where you can get the tables (for free I hope) for a city state zip comboboxes, or at least the tables for all cities in the US.
View 8 Replies
View Related
Jul 11, 2007
Hi
My name is Dana and this is my first post here .
I am a DB newbie , i started to teach my self MS Access with a small project as a start .
I made a simple client data table where there are country and city fields + phone , fax , etc etc ... .
I made 2 tables 1 for the countries and 1 for the cities .
I know that it is a very familiar question :o
How can we do the following
when you select a country in the client table , only cities of this country show up in the city combo box .
I found some answers here and there , but as a newbie , the codes where difficult to understand .
I attached my source file and i will be thankful if someone could help and to give me a simple explanation of the codes concept .
I just want to add the codes in the table not in a form , i found some answers by adding codes to a Form :confused: . Because as i know i can export only tables and queries to sql.
View 7 Replies
View Related
Jan 11, 2008
Ok Im having trouble with this, I need to remove cities from an address field but don't know a simple query that does this. Can any body help
heres an example
Address1 City
10/F, HONG KONG LAI CHI KOK EXCHANGE IIHONG KONG
Now I want to be able to pull Hong kong out of the address1 field but without searching in the address field with *HONG KONG* as this would involve checking every city in a table.
Please help!
View 1 Replies
View Related
Feb 21, 2007
Hi All!!
I need help with the code for a combobox. The thing is that I've created a form where the user has to select the country, then the state and finally the city. What I need is that once the user selects the country, the combobox of the state shows only the states of that country and then the combobox of the city shows only the cities of the chosen state. I'd appreciate all the help given. Andrew.
View 1 Replies
View Related
Mar 1, 2005
I'm stuck on something that should be simple, but maybe it isn't:
Problem 1:
To speed data entry in a contact table, I want an entry in the Zipcode (postal code) field to automatically populate the City and State fields from the data in a Zipcode table (Zipcode-PK, City, ST (2 letter code)). The Zipcode table has only one city /st for each zipcode, so it can be the primary key (always a five digit number, sometimes with leading zero(s)).
Sometimes there are several names for a City associated with a particular Zipcode, so I want the City field to be able to be overwritten with a different city name if the data entry person needs to do that. Alternatively, I could establish a separate primary key for the zipcode table, and allow several choices for the rare zipcode that has more than one city name in use.
If I did that I wouldn't have to store the city and st in the contact table (which is better database design, I realize), but I really do want to be able to store the city and state in the contact table because there will be times when the right zipcode isn't known, and the city and state are known, and forcing the input of a possibly incorrect zipcode would be undesirable.
Problem 2: I've got a table with 4,000 records where I have the zipcode, but the city and state information were lost, and I want to be able to re-populate those fields by writing, I guess, an update query that taps the info in the zipcode table.
Problem 3: When entering addresses, sometimes there won't be info about a certain zipcode, and I need some automatically generated dialog that allows adding city and state info for a new zipcode that won't slow down the entry of contact records.
Any help would be greatly appreciated. Thanks in advance. Send an email if you prefer.
HenryTD
View 3 Replies
View Related
Mar 8, 2015
I use Access 2003 and want to make a query by specifying the start and finish within the city, I send files. a
Attached file ...
View 1 Replies
View Related
Aug 6, 2014
I have a task of managing hardware assets like desktop, monitor, HDD, routers etc. Each item is marked with a unique ID.
These items could be transferred from one city to another city; so need to track that too.
1. There will be two tables
One (say AssetDetail) will have the following fields
RecordID
City
AssetID
Description of the asset (desktop/monitor etc)
ProductSerialNumber of the asset
Second (say AssetTransfer) will have the additional fields
NewCity
AssetTransferredDate
2. I only want to open a single form :
a) where on the basis of either AssetID or the ProductSerialNumber, I could open a particular record and edit it.
i) if the asset is transferred from city A to city B; then the "City" field value in AssetDetail table should change to "city B"(New City field value).
ii) At the same time, each asset transfer record should get appended to the AssetTransfer table even if the table has an existing record with the same AssetID or ProductSerialNumber.
3. Also need an option to make a new entry to the AssetDetail table from the above mentioned single form.
View 4 Replies
View Related
Apr 13, 2006
I have a table tblBookings.
In this table it has a bookingID, CustomerID and some other none relevant details.
The CustomerID comes from table tblCustomer. i.e a customerID must exist in the customer table to be allowed in the bookings table tblBookings
A customer can exist in tblCustomer without existing in the booking table.
I am trying to write a query that will list each and every customer ID in the tblCustomer and count the number of bookings that that customer has (even if it is zero).
I have a query that will count the bookings if they exist in the booking table and display the number of times that a customer appears in the bookings table.
SELECT tblBookings.CustomerID, Count(tblBookings.CustomerID) AS NoOfBookings
FROM tblBookings
GROUP BY tblBookings.CustomerID;
How do I create a query that will do this but list all customers even if they don't exist in the bookings table (but obviously occur in the customers table)
I am trying to create a similar query where all bookings per hotel are listed even if no bookings are made for that hotel. I am guessing the answer is the same as above.
The Ritz. Bookings 0
The Hilton. Bookings 3
The Carlton. Bookings 0
The Lowry. Bookings 2
For every hotel.
That kind of thing.
If you need more information please shout.
View 3 Replies
View Related
Aug 16, 2015
I have a table that has 5M+ accounting line entries. Below is an example of one accounting journal in the table.
BUSN_UNIT_IJRNL_DJRNL_ICNCY_CMONY_A
CB0014/07/20140002888269323AUD16797
CB0014/07/20140002888269323AUD-16797
CB0017/07/20140002888269323AUD16797
CB0017/07/20140002888269323AUD-16797
The journal ID above was an accounting entry, debit $16,797 and credit $-16,797. because it was entered as a reversing journal in the system, the table has captured the Journal ID with 2 dates. For my purpose i only want the one date (MIN) date, the total amount of the journal (either the debit or credit amount 16,797) and the total number of lines the journal ID has so in this instance I want the count to be 2 and not 4.
Right now this is what i get
BUSN_UNIT_I JRNL_I CNCY_C SumOfMONY_A CountOfJRNL_I MinOfJRNL_D
CB001 0002888269 AUD 0 4 4/07/2014
This is the output i would like
BUSN_UNIT_I JRNL_I CNCY_C SumofMONY_A CountofJRNL_I MinOfJRNL_D
CB0010002888269323 AUD16797 2 4/07/2014
Im thinking with the total sum because theres debits and credits is there a way to do the absolute value of the journal MONY_A then divide by 2?
current SQL
SELECT [One Year Data Lines].JRNL_I, [One Year Data Lines].CNCY_C, Count([One Year Data Lines].JRNL_I) AS CountOfJRNL_I, Min([One Year Data Lines].JRNL_D) AS MinOfJRNL_D, [One Year Data Lines].BUSN_UNIT_I, Sum([One Year Data Lines].MONY_A) AS SumOfMONY_A
FROM [One Year Data Lines]
GROUP BY [One Year Data Lines].JRNL_I, [One Year Data Lines].CNCY_C, [One Year Data Lines].BUSN_UNIT_I
HAVING ((([One Year Data Lines].JRNL_I)="0002888269") AND (([One Year Data Lines].CNCY_C)="aud"));
View 9 Replies
View Related
Sep 20, 2005
Hi,
Just spent the past hour in here trying to nut this one out, but not sure I've found something quite the same...though I know the answer will be painfully simple.
I have a customer table and a product table, and a query that groups customer first and last names along with a count of products per customer e.g. 1,1,3,2,3,4,2,1 indicates customer A buys qty 1 of product z, customer B buys qty 1 of product x, cust C buys qty 3 of product y and so on.
All I need to do now is do something to also output the total number of products. ie as per example above, 1+1+3+2+3+4+2+1 to get 17.
Can I do a count of the count or do I do some sort of sum of the count results?
I've tried everything I'm capable of as a newbie, and I'm not having any progress.
Any help appreciated.
View 2 Replies
View Related
Sep 5, 2006
Hi all,
Do someone have a way to do a count function in a Reports to let the report showing the details at the same time do the accumulating of the number??
I had try so many way but it not work~~!!! Pls i need someone help cause i stuck my report there without the accumulating of the number, Thanks.
Regard,
alex
View 4 Replies
View Related
Sep 12, 2006
I having trouble trying to figure out how i can get totals in a report, the report picks the information up from a query i have set up. The report shows various data to do with Grades awarded as part of an audit process (1-4), i want to put a total in the report to count how many 1's, how many 2's etc. i not very experienced with access so can anybody help me with this.:confused:
View 1 Replies
View Related
Sep 10, 2007
So i have 2 fields(124816 records)
IMKEY, DOCBREAK
IMKEY is like an ID. And docbreak is like a page counter, where some records are empty and some arent(seperated by D's and some C's). im trying to find out 2 things
1> Count how many values are within each group in DOCBREAk.
example:
DOCBREAK DOCCOUNT<--trying to figure out
D 3
<EMPTYREcord>
<EMPTYrec>
D 1
D 4
<EMPTYrec>
<EMPTYrec>
<EMPTYrec>
C 1
I tried this query, but it counts everything, i just want to count how many values are within a group(C's and D's
Select COUNT(DOCBREAK) from Jan003;
in excel, i could of done it, but since excel has a limit how many rows it can support, i had to do it in ACCESS...
2>in the IMAGEKEY column, since DOCBREAK seperates and makes groups, im trying to as well get the beginning number and the ending number(1st and last number).
ex
IMAGEKEY Beg End
1 D 1 3
2
3
4 D 4 4
5 D 5 8
6
7
8
9 C 9 9
i did it in excel, but then again, for my personal use, i would like to know how to do it in access
heres how i got the 1st number in excel(A=imagekey, and B=Dockbreak)
=IF(B1="D",A1,"")
end number (C=DOCCOUNT)
=IF(C1="","",OFFSET(A1,C1-1,0))
can anyone help me out??
View 6 Replies
View Related
Apr 20, 2006
I have 11 sites and I'm trying to missed visits at each site.
Currently I'm counting all non-missed visits at the site, and in the report I'm subtracting that number from Total # of patients at the site. This works fine - but there has to be a solution to the more "direct" method described below.
If I count up the actual missed visits and one site does not have any, the result only shows 10 sites and their totals. If the total count of missed visits = 0, is there a way to have access return that result?
I've tried changing the join relationship, inserting IF ([CountOfDay 30 Missed]) Is Null (or is 0) THEN .....
I know my naming conventions suck - I'm learning as I go, and I'm afraid to go back and change things now. Lesson noted for my next DB......
View 6 Replies
View Related
May 28, 2006
Hello,
Alright heres a problem for you's.
I have a table which holds roster information for staff. For each day in a 2 week period, there are 3 checkboxes, 'am' 'pm' and 'nt'. I need to perform a count on how many are 'am' 'pm' or 'nt'. For example, if 3 staff members worked on monday week 1 pm, i need it to return '3'. Problem is i am unsure how to perform a count on multiple fields?
Cheers
Derek
View 2 Replies
View Related
Oct 25, 2006
I'm struggling with this one.
first, I do a total query to retreive the frequency of a certain attribute
eg This produces
Customer Depot Order
CustA North 12 orders
CustA South 8 orders
CustA East 10 orders etc
CustB North 9
CustB West 11
CustB East 10
So now I want to retrieve for each customer, the Depot with the highest order count
ie
CustA North (12)
CustB West (11)
I can't find the right structure for the second query to generate the answer, because as soon as I do a group by, I get all Depots again! Or can I do the whole thing with one query.
If there are two similar max counts, ideally, I want to return either one. I suppose I could do a dlookup on the max count to retrieve the associated depot, but this seems sloppy.
View 5 Replies
View Related
Jan 16, 2007
I have two tables - one is the inventor name, one is the #patents and the business unit. (in a nutshell - these are the only fields I need from each)
I need a query to return the results:
Inventor Name #Patents BU
Joe Smith 28 W
Joe Smith 4 F
what I get is:
Joe Smith 1 W.....(28 times in a column)
Joe Smith 1 F...(4 times in a column)
I've read and studied and queried and tested until I can't see. This can't be that hard. Can someone help?
View 3 Replies
View Related
Mar 14, 2007
Hi, I need to count how many times FarbeBC changed in value.
I don't need how many different values. That would be 4. But it only changed 3 times.
I managed to do this in excel. But can can't find out how to do this in access.
Any help from you guys?
I've Access 2000 with an ODBC connection to an SQL server.
DateEVT | FarbeBC
13/03/2007 06:14:09 | -M7X
13/03/2007 06:16:12 | -M7X
13/03/2007 06:17:12 | -92U
13/03/2007 06:18:35 | -92U
13/03/2007 06:20:53 | -92U
13/03/2007 06:21:57 | -92U
13/03/2007 06:23:00 | -92U
13/03/2007 06:24:31 | -92U
13/03/2007 06:27:24 | -92U
13/03/2007 06:28:43 | -92U
13/03/2007 06:29:47 | -84A
13/03/2007 06:31:09 | -M7X
View 2 Replies
View Related
Feb 29, 2008
Say I have the following database in Access:
Letter Name
(blank) Joe
c Joe
c Joe
c Sue
d Joe
c Sue
How can I know the know letter count without duplications for each unique name?
So for Joe I want it to return 3, (= blank + c + d).
For Sue it should return 1, only c is unique.
TIA!
View 8 Replies
View Related
Apr 29, 2008
Need help
When I try running the query below I get this error.
"Reserved error (-3025); there is no message for this error"
SELECT
totalelectricemergencies_1 = (SELECT Count(electricemergencies_1) FROM Phase_1 WHERE electricemergencies_1 ='Always'),
totalelectricemergencies_2 = (SELECT Count(electricemergencies_2) FROM Phase_1 WHERE electricemergencies_2 ='Always');
Any ideas?
View 4 Replies
View Related
Feb 18, 2005
I have a form, with a tabbed interface. On each tab is a subform showing a continuous form with records matching criteria.
I'd like for the name of each tab to also include a count of the items in each subform on each tab. Is that possible?
So the name of tab 1, instead of "Opened Cases" would be "Open Cases (X), where X is a count of how many cases are currently open.
Thanks.
View 3 Replies
View Related
Jul 1, 2005
I wonder if someone can help me with a count function. I ahve looked at the help files, but I cant understand it. Basiclly I have a number box on a form, I need to add to gether the numbers from each record to form a total.
View 4 Replies
View Related
Oct 23, 2005
I am having trouble counting the number of fields that contain a value greater than £0.00 for example I have five fields in a form £5.00 £2.50 £0.00 £3.00 £4.00 I have tried nz =1 >1 my current sting is =count([price]) which results in a count of 5 but I don't want £0.00 counted......
View 3 Replies
View Related
Mar 12, 2006
Hi,
I have a question.
Suppose there is a table:
Vendor__________Code ......................
A________________1
A________________2
Dcount("Code","table","Vendor = A") = 2
It is fine.
Vendor__________Code ......................
A_______________1
A_______________1
A _______________2
Dcount("Code","table","Vendor = A") = 3
I want to count the different between the code, it should be 2.
I don't want to create the query to group it and then count the query, because the values of the rest of the fields of the table may be different.
Does any function for count different? Thanks.
View 3 Replies
View Related
Jul 14, 2006
Hi, I have a table which holds the months, and a distinct list of order_id.
On My Form I have a combo box where it displays the months of the year,
What i like is for when i select a month from the combo box for it to display the total count for that month.
View 5 Replies
View Related
