I have table when I open it I found total number of records 1000 while I get result through query I found that 2 records or more than present in the Table mean in query number of record is 1002. I donot understand why these tow records are not seen in the actual table. and these are not deleted by delete record qurey. While I can see it in select command.
View RepliesI have created a booking system for a set of resources for schools. Most schools have a membership which entitles them to 2 free sets. I have a booking form with a membership subform (membership table), and a booking details subform (kitloan table).
Once a school is selected on the main form, the membership subform shows the most recent record for that school based on schoolID.I want to display the number of sets they have already had within their membership period (can start at any time of the year, and lasts for 1 year) on the membership subform, so we know how many free ones they have left.
I therefore need to count the number of KitBkID (ID of the booking) in the Kitloan table where SchoolID = the SchoolID displayed on the membership subform, and the DateOut (booking date on kitloan table) is between the DateJoined and DateRenewal displayed on the membership subform (from membership table).
I can do this with a query which works when run and provided with the parameters SchoolID, DateJoined, and DateRenewal.
SELECT Count(Kitloan.KitBkID) AS CountOfKitBkID, Kitloan.SchoolID, Kitloan.DateOut
FROM Kitloan INNER JOIN Membership ON Kitloan.SchoolID = Membership.SCHOOLID
GROUP BY Kitloan.SchoolID, Kitloan.DateOut
HAVING (((Kitloan.SchoolID)=[Me].[SCHOOLID]) AND ((Kitloan.DateOut) Between [Me].[DateJoined] And [Me].[DateRenewal]));
What I can't do is get it to run on the form and take those values from the form.From the searching I've done, I'm thinking a DCount should be the way to go, but I cannot get the criteria right. I created a query (KitloanCountQry) so that criteria could come from both the kitloan and membership tables.
SELECT Kitloan.KitBkID, Kitloan.SchoolID, Membership.DateJoined, Membership.SCHOOLID, Kitloan.DateOut
FROM Kitloan INNER JOIN Membership ON Kitloan.SchoolID = Membership.SCHOOLID;
I have put the DCount as the control source for a textbox on the Membership subform (but have tried it in VBA too):
=DCount("KitBkID","KitloanCountQry")
This works but obviously gives me the total for all bookings.
[code]....
Although I have to admit to getting lost in the syntax. This produces #Error.
Here is my issue. In a table with an Auto Number index some records have been deleted. I have been able to recreate them along with their original auto number. The problem is that I do not know how to append these records forcing the original auto number. I have tried changing the auto number field to a number field in the table, this works except I cannot change it back to auto number.
I am sure I’m not the first with this question or issue. I did search through a couple hundred entries about auto number before I posted this question.
Jim
We want to send letters to clients on behalf of our salespeople on a weekly basis from a list of clients in a table. But some salespeople will have 5 letters go out and some will have 20 go out (depending on what amount the salesperson is able to followup on). I can store the weekly letter quantity number in the table no problem, but I am just not sure how to have the query show 5 letters for Bob and 20 letters for Sue.
I know about the "Top N" setting in query design, but not sure how to set it to read from the table.
Any ideas?
I am pretty new to MS access and have a question that I have not been able to figure out. I am making a database with a form to input the data in to the database. The only thing is I want to be able to search/lookup/record data by the products ID code and not by its record number. For example, product 1234 instead of entry number 1. How would I go about this? Thanks for your time!
View 1 Replies View RelatedI'm sure this should be simple but I can't figure it out. What I want is for a text box on a form to display the number of records within a table.
Basically I've got a make table query which creates a table ox X records depending on how many records in the original table match the criteria. So for example:
Table A has 100 records
Query identifies 30 that mee the criteria
Makes a new table called criteria_met with these 30 records
What I want is for a textbox on a form to display "30" and update whenever the make table query runs (probably via a macro??)
I'm after some code as to how to count a number of records within a table, I then want to have this number returned into a variable so I can determine usages with it, e.g. If the value is < 1 and using conditional statements.
View 1 Replies View RelatedWell boys and girls, its about that time in the week where I have once again come up with a brilliant application for my little Access project, but have no idea how to make it happen.
I will start with a little background info... In the course of the parts recoveries we have different reasons for recovering parts, different people (locations) that request them, and different priorities for recoveries. For example, when a new model comes out, we will recover 100% of the parts for investigation by the factory... however, we may want to take a look at failed water pumps first. In this case, our recovery, until it is complete, will have higher priority than the 100%, otherwise we would not receive any parts.
This is because of the way our warranty system works... It assignes a three digit number to each part number of a recovery (between 1-999). The lower the number, the higher the priority.
So if I am setting up a 100% recovery, I would want to put it in the, say, 200's. This would leave room for an overlapping recovery to be inserted in the 100's where it would divert a certain number of parts away from the normal recovery stream. Then, once a user-defined date or quantity of parts has been reached, the corresponding number goes dead, and any of those part numbers are reunited with the original recovery stream.
Sticking to the example above, typically 100% recoveries would go for say... 2-3 years, where as an in-house recovery might be for 20 parts... Another difference is that the longer term recoveries are managed by the end date, where shorter term recoveries are by number of parts recieved.
What I want to do is be able to auto assign these numbers based on criteria derived from user-inputted data... (i.e. destination, start/end date, recovery type, etc.). Then, when the recovery is complete, whether based on date or quantity recieved, to delete the information associated with the 3 digit number, block its use for 45 days, and then be able to reuse the number for a new recovery.
Whew.
Thanks to anyone who can be bothered to read past the first paragraph, let alone offer any advice.
I have a table which specifies the delivery date
I have a from that allows you to choose a year and a month.
I have an unbound textbox which I wan to display the count
I want to be able to count all the records from a table with the year and month specified in the comboboxes and display this in the texbox.
I am looking to make a table which adds a sequence number to my records.
Example data would look like this
Acc no Date
1234535 01/01/2013
1234535 05/01/2013
1234535 11/01/2013
9876543 02/01/2013
9876543 22/02/2013
9876543 01/03/2013
I want it to add a deq based on the account no then date in sequence oldest to newest.
Acc no Date Seq
1234535 01/01/2013 1
1234535 05/01/2013 2
1234535 11/01/2013 3
9876543 02/01/2013 1
9876543 22/02/2013 2
9876543 01/03/2013 3
Hi.
I have a form (frmdetails) to store job details and another form (frmMaterials) to add materials to each job. They are both linked via a field Project_No
I have a query (QryTotal)that when run will list the materials used for each project, that is all OK.
What I am tryng to do, is add a text box to frmdetails, that will just tell me how many materials have been booked against each job. Just a running total that will tell me that for project number 100000, 4 items have been booked.
I have tried using subforms (using =count(*) ) but this doesn't seem to be working. I also added =Count(QryTotal!Material) to a text box but this doesn't work.
Can someone help?
Thanks.
Frank.
Hi,
I have a table with a field that shows the number of pieces that a parcel contains. It looks like this:
ParcelID, Pieces, Description
Data example:
1001, 5, Jackets
1002, 10, shoes
etc
I need to print labels for each piece that shows to which parcel it belongs. The report will look like:
1001, Total pieces = 5, 1 of 5 pieces, Jackets
1001, Total pieces = 5, 2 of 5 pieces, Jackets
1001, Total pieces = 5, 3 of 5 pieces, Jackets
1001, Total pieces = 5, 4 of 5 pieces, Jackets
1001, Total pieces = 5, 5 of 5 pieces, Jackets
and
1002, Total pieces = 10, 1 of 10 pieces, shoes
1002, Total pieces = 10, 2 of 10 pieces, shoes
1002, Total pieces = 10, 3 of 10 pieces, shoes
1002, Total pieces = 10, 4 of 10 pieces, shoes
1002, Total pieces = 10, 5 of 10 pieces, shoes
If I could populate a table where the number of records equal the value of the pieces column then it's a pretty simple report but I can't figure this out.
Can anyone help?
I have a form which allows the user to add new records to a table. After the user had entered all the information into the form, they click a command button to add the record. In addition to adding the new record, my command button runs an query which is supposed to generate a random number between 1 & 1,000,000,000 and update the record ID field with that number.
Here is the formula I have been using in the "update To" now of my query: Int((1000000000-1+1)*Rnd()+1)
My problem is that I keep getting duplicates. You would think that the chances of getting a duplicate number would be pretty small with this large of a range, but I get a duplicate almost every time.
I have tried indexing (No duplicates) the field in the table, but that did not work. When my query generated a duplicate number, the record was just not added to the table.
I also tried a two step approach:
1-Make a table of all in use record ID numbers from my table (tblIdNo)
2-Update new record with a random number that is not in tblIdNo
This was a no-go too
How to build an update query that will update each new record added to the table with a random number between 1 & 1,000,000,000 without any duplicates? This seems like it should be so simple, and I am starting to get really frustrated.
I would prefer to accomplish this through a query/queries (if possible) rather than with 100 lines of code. This database is not for me, it's for another group, and the individuals in this group are totally freaked out by code.
Hey all, i just would like to know if there is a way to have a fixed number of records in a query.
Let's say, example (a), a query returns 2 records (qry1); i would like to have my query (qry2) to have 5 records, so the first two records will appear and then 3 blank records. (b) A query (qry1) returns 4 records, then this query(qry2) should return the 4 records plus a blank record
.........qry1.......qry2
a)|>record1....|>record1
...|>record2....|>record2
...|*______....|>______
....................|>______
....................|>______
....................|*______
.........qry1.......qry2
b)|>record1....|>record1
...|>record2....|>record2
...|>record3....|>record3
...|>record4....|>record4
...|*______....|>______
....................|*______
Why do i want to do this?
Because i want my report to show 5 records so when i print it i have those blank spaces to fill if necessary.
I want to have this:
______
lrecord1l
lrecord2l
l______l
l______l
l______l
Instead of:
______
lrecord1l
lrecord2l
What im doing now is creating a temporary table and add the records to it plus blank records.
What i used to do was add blank records in the same table meting the criteria of the report, so they will appear. After close the report, those records would be deleted.
I would like to know if theres another way, like a command in sql, or maybe some property on the report. What ever is most efficient and a good database practice. Thanks in advance
Hi all,
I want to retrieve a set number of records from a table, using a query. I'm not sure of how to do this.
I can't use the ID, because the table gets its data replaced contantly by imports (so the ID doesn;t start at 1...)
I want to be able to retrieve, say, the first 25 records on that table.
Any help is appreciated
I need to extract a specific number of records into a table using a MakeTable or Append command using a temp variable, e.g. TempK&SA. Previously on the forum I was shown how code could be added to the OnOpen function to use a temp variable to select a specific number of records to report. ACCESS does not have the OnOpen function in the design view of a query like in the report. It does allow a SELECT TOP but only with fixed variables or percents (e.g. 25 in the code below).
The beginning code for the make table query (where 25 is the number of records added) is:
INSERT INTO [Output] ( RndNo, PointBiserial, BloomsTax, DateRevised, Exam1, Status, Exam2, Exam3, Exam4, [NCCPAKnowledge&Skills] )
SELECT TOP 25 TestBank.RndNo, TestBank.PointBiserial, TestBank.BloomsTax, TestBank.DateRevised, TestBank.Exam1, TestBank.Status, TestBank.Exam2, TestBank.Exam3, TestBank.Exam4, TestBank.[NCCPAKnowledge&Skills], *
FROM TestBank
WHERE (((TestBank.PointBiserial) Is Null Or (TestBank.PointBiserial) Between [TempVars]![TempPointBiserialLow] And .....
how to modify the code to allow a temp variable to determine the number of records to append to another table would be gratefully received. (This process then is repeated for a total of 7 append tables with different temp variables.)
I am looking for a query that will return records from a table that have related records in another table. Opposite to the Unmatched Query Wizard.I have two tables: tblSupplier and tblSupplierProducts.The two tables are related by the field "SupplierId".I need the query to only return Suppliers that have Products.
View 3 Replies View RelatedHi,
I would like to count the number of records returned from a query, but am unsure how to do this using VBA.
Any help appreciated.
Thanks
I have a stock record database which I have 'inherited' from someone far cleverer than me!
I'm fairly used to basic SQL, but I'm teating by hair out over this particular problem.
I'm writing an Update Query to count the number of [NewModelCode] records WHERE Status ='Available' AND StockType = 'New' AND InstStockReference.OnlineShop = Yes and update a field called CountNew on a table called InstStockReference..
So far I have the following SQL:
UPDATE InstrumentStock INNER JOIN InstStockReference ON InstrumentStock.NewModelCode = InstStockReference.NewModelCode
SET InstStockReference.CountNew = DCount("NewModelCode","InstrumentStock","Status='Available'")
WHERE (((InstrumentStock.Status)="Available") AND ((InstrumentStock.StockType)="New") AND ((InstStockReference.OnlineShop)=Yes));
It partly works, but the result I get is a column count of 939 in every field where the Status ='Available' AND StockType = 'New' AND InstStockReference.OnlineShop = Yes.
I want to do a count the number of [NewModelCode] records.
Something's gone wrong? Any ideas?
Hi all,
Is there any option to count the number or records relating to
a specific field. Here is the details..The db is for a school and there is a tables for
class and students. I am using a form to enter students details. I had created a
relationship with these two tables. i am using a query to get the details regarding a specific
class like classname, class teacher like that.. Is there any way to calculate the number of students in that specific class.
now i am using the query as follows
SELECT Class.*
FROM Class
WHERE (((Class.ClassID)=[Forms]![Class]![ClassID]));
I had created the relationship with the above two tables with classid as the primary key
thanks in advance
Thanks
Hello all. I need some help calculating the total number of records returned in a query anytime i run the query.
I have a form that creates an SQL statement and changes the querydef at runtime and displays the results in a listbox. I want to include on my form a percentage of the total records from the table based on the records returned in the query Ex. (5/10) so I can use this information on a report.
What I am trying to ultimately acheive is to calculate an attrition rate based on the number of (clients that leave in a given period / total new clients added in the given period )
thanks for your help!
tony-t
I have a form based on a query. I've disabled "Navigation Buttons" on the form and am trying to recreate their functionality in a little more user friendly way. I've created next / previous record buttons and have those working great.
I'm now trying to re-create the record counter / tracker. I'd like to setup a box that shows which record I'm on (this can simply be based on the order the query returns them, the same way the navigation buttons does it when they are enabled. ), and how many records there are total in the query.
I tried messing around a little bit with =DCount but wasn't able to make it work I suspect because I'm counting the number of records returned in a query, not in a table.
If I have a table called "Calls" with an autonumber and another field with names for stores like this:
1 Hobby Mart
2 Peter's Store
3 Hobby lobby
4 Hobby Mart
5 Peter's Store
How can I build a query to count the number of different store names?
For example, in this case I need the query to return the number 3.
I have a date field in a table. I wish to display in a query:Column A representing the month. Column B representing the number of records in the table corresponding to that month.
View 5 Replies View RelatedI have been tasked to create a DB for my unit. I have created a few DB, but I am a novice at best. I need a crosstab Query to count the number of records for each FY. The Army's FY is from Oct -Sep. I only need to show the the total number of record for the previous FY in a Report and on a form.
View 7 Replies View RelatedI build a database to manage some events. To simplify things, let's assume an event takes place on 3 days, from 01-05-2013 to 03-05-2013.
I create a table named EVENTS.
Among other stuff, I create the following fields:
EVENT_first_date
EVENT_last_date
Then I create a query which returns the number of days the event will take place on.
EVENT_total_days: [EVENT_last_date]-[EVENT_first_date]
I use an append query to insert this new record into the table EVENTS_MAIN.
I wish to set the start time and end time for all event days in hours and minutes. I will use these entries for displaying start time and end time for each event days, to calculate daily total hours and event total hours later on.
Therefore, I wish to create a query that will display event date 1, event date 2 and event date 3, based on the number of the EVENT_total_days field, so I can set the start time and end time for each day separately.
Is it possible to "convert" or "switch" the number of the days into number of records? Can a query convert a field value into number of records?