I have a table with quite a number of records.
In this table I have a field [Qnumber], which gives me a sequential number.
The field size is Long Integer. So my records are numbered 1, 2, 3, ....n
What I want to achieve is to select only the even numbered records in this table, i.e. Qnumber = 2, 4, 6, ...... etc.
I can't find a way to do this.
Would appreciate any help on this.
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.
Hi,
I have a table with 400,000 (approx) records in the following format:
a(PK:AutoNum) - b(text)-c(text)-d(Num)-e(text) - f (text - unique/random)
A B C D E F
1 - 6767 - P2 - 1 - 24/992 - 34341212
2 - 6767 - P2 - 1 - 24/993 - 87657483
3 - 6767 - P2 - 1 - 24/995 - 98764536
4 - 6767 - P2 - 2 - 24/996 - 87543297
5 - 6767 - P2 - 2 - 24/998 - 98674635
6 - 6767 - P2 - 2 - 24/999 - 34546576
7 - 6767 - P2 - 2 - 25/001 - 98768547
8 - 6767 - P2 - 2 - 25/002 - 46576897
9 - 6767 - P2 - 2 - 25/004 - 62536475
I need to create a new field in a query that acts like an autonumber, that resets based on an increase in field (d).
ie:
1 - 6767 - P2 - 1 - 24/992 - 34341212 - 1
2 - 6767 - P2 - 1 - 24/993 - 87657483 - 2
3 - 6767 - P2 - 1 - 24/995 - 98764536 - 3
4 - 6767 - P2 - 2 - 24/996 - 87543297 - 1
5 - 6767 - P2 - 2 - 24/998 - 98674635 - 2
6 - 6767 - P2 - 2 - 24/999 - 34546576 - 3
7 - 6767 - P2 - 2 - 25/001 - 98768547 - 4
8 - 6767 - P2 - 2 - 25/002 - 46576897 - 5
9 - 6767 - P2 - 2 - 25/004 - 62536475 - 6
I initially thought I could make an IIF expression that basically says: if [d] in this row is the same as [d] in the previous row then add 1 to the previous record [new number], otherwise, enter 1 in this record [new number] (thereby resetting it)....
I have searched the forums for 'numbered sequences' (amongst other things), and it appears that either the Dcount or Dmax function appear to be the solution, but I'm having some trouble applying it to this problem. I was hoping someone can help me through it.
Cheers
I have a report listing all individuals within my database by their score. I need to select the top say 40 people from the list, instead of counting down 40 from the top how could I put a number beside each name? Any help would be greatly appreciated.
View 12 Replies View RelatedI have a inventory list that i export from another programe
it exports the lines like so:
ITEM DESCRIPTION PRICE QUANTITY (this line does not get exported)
1021 60BL+40PI+20SL 11$ 120
1022 50BU+30SL+10BZ 8$ 90
CR5415 50BZ+50BL+50SL 5$ 150
Now the program only calculates the quantitys when a order is submited into it.
it does not subtract the quantity on the color in description.(i am the one that mannualy added the text in description wiche is my pysical inventory)
I am able to export the lines from the orders to another table wich looks like so:
1021 05SL 11$ 5
1021 10PI+10SL 11$ 20
1021 02BL+5PI+5SL 11$ 12
Now the description is again me just mannualy typing the text into the order in description and then the quantity.
the program then just minuses the quantity from the inventory list.
now what i hope to acomplish is be able to take the invetory list, and substract the LineOrders Description from it,
and then just give me the new inventory list.
I figured it goes somthing like this but im not to sure.
If [InventoryItem]=[LineOrderItem]
Then
Compare
If __@@ from [InventoryList]is the same as __@@ from [LineOrder] then subtract ##__ from ##__
And i hope to get at the end my new inventory list in this case it would be
1021 58BL+25PI+05SL
I dont mind about the total quantity since the other program does that for me.
If anybody can help me it would be really apreciated.
I hope i have made sense, if i have not please tell me and i will try again.
thank you
Mark
My friend extracted some text from a PDF file, pasting it into Excel, and needs to find certain pieces of information. Problem is, the text came out as a long string instead of being broken into cells.
So, what we need to do is find, within the text, the 1st, 3rd, 5th, 7th, 9th, 11th and 13th times the word "Principal" appears, and then report back the name that follows. Within the text, it would read something like
(1234 Principal John Doe)
The number in front of it will change or be in a different format, there are other parentheses in the text (varying number of times within the text), and the name changes, of course. We want to extract the name following the word "Principal"... but only the odd-numbered times they appear. Some cells will have as few as six "Principal" entries, others as many as fourteen.
I am trying to create a named hierarchy based off a table from an Oil & Gas Program ARIES. The code below is as far as I could get:
Code:
SELECT dbo_AC_ECONOMIC.PROPNUM
, dbo_AC_ECONOMIC.SEQUENCE
, dbo_AC_ECONOMIC.SECTION
, dbo_AC_ECONOMIC.QUALIFIER
, dbo_AC_ECONOMIC.KEYWORD
, dbo_AC_ECONOMIC.EXPRESSION
, (SELECT COUNT(*)
[Code] ....
The results are as such:
PROPNUM...SECTION...SEQUENCE...QUALIFIER...KEYWORD ...EXPRESSION...TEST
TEST1.......4................1.................TAG .............GTC/GAS.......03.................1
TEST1.......4................2.................TAG ............."..................22................ ..1
TEST1.......4................3.................TAG ............."..................22................ ..2
TEST1.......4................4.................TAG .............SHRINK.........1...................1
TEST1.......5................1.................TAG .............ATX...............5.................. .1
TEST1.......5................2.................TAG .............ATX...............5.................. .2
TEST2.......4................1.................TAG .............GTC/GAS.......03.................1
TEST2.......4................2.................TAG ............."..................22................ ..1
The desired Results:
PROPNUM...SECTION...SEQUENCE...QUALIFIER...KEYWORD ...EXPRESSION...TEST...KEYWORD2
TEST1.......4................1.................TAG .............GTC/GAS.......03.................1........GTC/GAS_1
TEST1.......4................2.................TAG ............."..................22................ ..2........GTC/GAS_2
TEST1.......4................3.................TAG ............."..................22................ ..3........GTC/GAS_3
TEST1.......4................4.................TAG .............SHRINK........1...................1.. .......SHRINK_1
TEST1.......5................1.................TAG .............ATX..............5................... 1........ATX_1
TEST1.......5................2.................TAG .............ATX..............5................... 1........ATX_1
TEST2.......4................1.................TAG .............GTC/GAS.......03.................1........GTC/GAS_1
TEST2.......4................2.................TAG ............."..................22................ ..2........GTC/GAS_2
First I will give background on the table and fields. Then I will explain the overall goal for creating of the TEST and KEYWORD 2 fields.
BACKGROUND
PROPNUM: UNIQUE ID
SECTION: Set of data responsible for a certain function EX: section 4 - expenses, section 5 - interest, etc.
SEQUENCE: The order of the sytanx in that propnum's section
QUALIFIER: Qualifies multiples set of syntax per section to differentiate other work (NOT REALLY IMPORTANT for the query)
KEYWORD: A specific word that the program recognizes and treates the expression according to the key word * the quotes keyword is a continuation line and represent the keyword above it*
EXPRESSION: are the variables that are treated by program according to the keyword
OVERALL GOAL: The main goal is to have a spreadsheet of variables used by the program to calculate it's end result. Which means KEYWORD & EXPRESSION by PROPNUM. THE PROBLEM is that the only way to tell that a quote keyword belongs is by having the sequence and section lined up. So my solution is to rename the quote keyword with the primary keyword and a number.
I have a union query with 2 fields: Order and Row_Heading_Full.
Code:
SELECT DummyClientType.Order, DummyClientType.Client_Type AS Row_Heading_Full
FROM DummyClientType
UNION SELECT ("25") AS [Order], ("Totals") AS Row_Heading_Full
FROM DummyClientType;
The purpose is to pull the Order and Row_Heading_Full fields from a table and add another entry that with "25" as the Order and "Totals" as the Row_Heading_Full at the end of the list.
I want it to be ordered sequentially based on the Order field. But instead it is ordered like this: 1, 10, 11, 12... 20, 21, 22... 3, 4, 5... In other words it is ordering only by the first digit and not by the number as a whole.
When I remove the union aspect it is ordered properly:
Code:
SELECT DummyClientType.Order, DummyClientType.Client_Type AS Row_Heading_Full
FROM DummyClientType;
But that defeats the purpose because I am not adding a final entry of "25" and "Totals".
I have a table (tblContact) with an auto number key field that is numbered sequentially (1-8) there are no deletions, each new record is appended.I wrote a simple FindFirst line to locate a record that is the first record in the table. The FindFirst failed to find the record.So, I wrote a Do Until Loop that cycles through each record looking for the record that I want to find (the first record with key field 1).
Code:
rstContact.MoveFirst
Debug.Print "ContactID: " & rstContact.Fields("ContactID")
Debug.Print "CEmployerID: " & rstContact.Fields("CEmployerID") & vbCrLf
Do Until rstContact.EOF
Debug.Print "ContactID: " & rstContact.Fields("ContactID")
Debug.Print "CEmployerID: " & rstContact.Fields("CEmployerID") & vbCrLf
This works to find the first record... eventually, because it does not begin its search at the first record. The results in the immediate window are below.
Code:
ContactID: 4
CEmployerID: 2
ContactID: 4
CEmployerID: 2
ContactID: 5
CEmployerID: 4
[code]...
I believe the field CEmployerID is unrelated to the issue. I am also attaching screen shots of the table "tblContact" and code with immediate window. I have tried indexing and not indexing the CEmployerID field in the Contact table to no avail. Even though the Do Until Loop eventually finds my record,
I have created a query that returns x number of records. How can i count this number (x)?
View 1 Replies View RelatedI have a field on my Project form(form is based on a query) which is called Completed (Y/N). It holds values Y and N.
I’m wondering if there is a way to count how many Projects completed and how many didn’t. I’m not sure how to count just Ys and then just Ns.
Any help would be appreciated.
Thank you!
HI there
How to set up textbox which will count how many records is already in my table and add one more and display that in my Form????
cheers
Natal:)
I have "Age" field from 10-100 years old. My problem is I want to count how many are young(21 below), middle aged(22-45), and old(46 above) in a report. How am I going to write it in a textbox? Please help me and teach me step by step how.
View 8 Replies View RelatedYes i'm having trouble with a Count problem, don't laugh at me.
I've got a query with 200000 plus records. Each record has an equipment ID number (its not the primary key) in column B. I just want to count up the number of records that have each equipment ID code, but im not sure how to do it.
Please help
I have an order database using Access 2000, I need a report to list all agents that have ordered in the past year, how many times they ordered and sort by Office city.
I have
SELECT tblCustomers.Type, tblAgentList.AgentID, tblAgentList.LastName, tblAgentList.FirstName, tblAgentList.Agency, tblAgentList.AgencyCity, tblAgentList.DeliveryAddress, tblAgentList.DeliveryCity, tblAgentList.DeliveryState, tblAgentList.DeliveryPostalCode, tblAgentList.[AgentOffice#], tblCustomers.InspectionDate
FROM tblAgentList INNER JOIN tblCustomers ON tblAgentList.AgentID = tblCustomers.AgentID
GROUP BY tblCustomers.Type, tblAgentList.AgentID, tblAgentList.LastName, tblAgentList.FirstName, tblAgentList.Agency, tblAgentList.AgencyCity, tblAgentList.DeliveryAddress, tblAgentList.DeliveryCity, tblAgentList.DeliveryState, tblAgentList.DeliveryPostalCode, tblAgentList.[AgentOffice#], tblCustomers.InspectionDate, tblCustomers.[Job#]
HAVING (((tblCustomers.Type)="P") AND ((tblCustomers.InspectionDate)>[Enter starting Date:]))
ORDER BY tblAgentList.AgentID, tblAgentList.AgencyCity, tblAgentList.AgentID;
It list all agents sorting by AgentID, with a line for each order.
I would like to list each agent once with the number of times ordered, and then sort by AgentCity. Any help would be appreciated... Thanks in advance!:D
Hi,
I've been going round in circles a bit with this, read a lot of interesting stuff posted, but can't quite answer my problem.
I have a query that returns 2012 records showing the project phase for every Employee assignment. How do I group & count the records so that I get the number of projects in each phase for each employee?
My group and count attempts so far have either reduced the record set to 79 (the number of employees with assignments) or simply returned a "1" for each record.
Any pointers appreciated.
Regards,
Hi
Been taking abreak from Access for a few years now and cant get my head round my problem.
I have been asked to manipulate data from a Training Recods database.
Basically, training consists of 26 modules, there are currently 180 people who need training. Each person has been assigned a unique number, once a module has been completed, the date it was completed is entered into the database. There are currently lots of modules which havent been completed, what I need is a way of pulling that data from the DB.
I need to know how many people need to complete each module. For example, Module 1 might need to be completed by 15 people, Module 2 might need to be completed by 27 people etc.
I have enclosed the DB with the table and the form that I am looking to populate with the data.
Anyone have any ideas, I just can't get my head round it!!!!
Hello, I am new to Access and trying to run a query that will count only unique invoice numbers in my table. I am sure this is an easy command, and thank you for your help!
View 3 Replies View RelatedHi guys,
I know how to count records so that null records would be counted but I do not know how to do this:
I have customers and works. I want to create a query that will show ALL customers and number of works done for that customer (sometimes there is no work done for customer).
How can I do it?
Thanks,
Matt
I have a table which essentially is:
- Category
- Item #
- Sales $
I'm looking for a query that will tell me the count of items that make up 80% of the sales per category (probably would have to use a subquery here). So if my table was:
item #, sales $
1, $10
2, $8
3, $6
4, $5
5, $1
Total sales = $30
80% of total = $24
The number of items needed to get to 80% of total sales = 3
Is there any easy way to do this in a query?
In the Form Footer I use a box with '=Count(*)' to count all records.
But how do I count only those records that have the value '?'
in the field 'MailRead'.
Thanks for the help.
I have a form that has a subform in it, which is another table in it. I want to add the number of fields that are in the subform. What is the best way to do so?
I would like to write a VB script so that a field in the master field = the count of the records in that subform. Any help is greatly appreciated. Thanks.
I have data which has a one-to-many relationship and I would like to display in a form (or webpage) both the one and the many side of the relationship and to display a count of the number of records on the many side which correspond to each individual record on the one side.
I attach a zipped .mdb file containing dummy data which includes a pair of linked forms showing where I've got to so far with implementing the idea. If the Words form is opened first, the linked form, Associations can be opened by clicking on the Associations button.
(Incidentally, I need to do something to control where and at what size the two forms open on screen. I think I've seen from a post on this forum that what I need is DoCmd.MoveSize.)
The problem I'm hoping someone will help me solve is how to get the count of the records on the many side to appear only once and, preferably in the Words form, not the Associations form. If you view the forms in the attachment, you will see that at the moment the count appears once for each item on the many side in the Associations form.
I'm a beginner and am unsure if the solution is very simple, perhaps involving referencing a hidden control in the Associations form with a path to that control comprising various identifiers joined with ! and or . , or perhaps using a simple snippet of Visual Basic, or whether I'm asking for help with something which is actually difficult.
Good day,
I have a sub-routine created on the form labelled frmenrollcenter and inside this routine I want to count the records in tblec based on the value in "txtextl" textbox. Then I want to display this value in a message on the screen. I was wondering if anyone knows the function that can make this happen.
Thanks
is it possible to count the time between records? example
Table set up:
LogID
StartTime
StopTime
ProblemCode
Example records:
Id -------> StartTime ------> StopTime--------> ProblemCode
1 -------->06/10/05 10:00 -> 06/10/05 10:02 -> A101
2 -------->06/10/05 11:00 -> 06/10/05 11:02 -> A131
i want to count the time elapsed between, ID 1 ending and ID 2 starting, can ido this?
if so how..
the duration i am looking for in this case would be 58 mins
cheers
Andy
Hello,
My database has two tables: MASTER1 and MASTER2. They are used for a list of clients. They contain general info like names, address and SSN.
I use MASTER1 to add new data while MASTER2 (via an append query) to save some data which does not need to be destroyed.
For both tables I have designed a form and each table has the ID as PK and a field SSN.
I would to add a function that while I am in form MASTER1, it could count via a control (txtbox) the number of records with the same SSN of the active form (MASTER1) in table MASTER2.
Any help? Thank you.