Restrict Number of Table Entries?

Hey all,

I'm having a horrible time trying to get this to work. Basically, I have a database which has multiple tables. All of my tables are being generated with a CREATE TABLE statement. What I would like to do is add a validation rule or something else upon creation so that only X number of entries can be entered into the table.

Initially, I just added a rule that said id


Post your answer or comment

comments powered by Disqus
Hi
I have a button on a form, which updates the numnbers of records in 4 tables.

When the button is clicked, it deletes all records in the tables, and then adds the records back in from another table that is linked.

Part of the code is below:-

----
Dim stDocName1 As String

stDocName1 = "Delete_LabourHours"
DoCmd.OpenQuery stDocName1, acNormal, acEdit

Dim stDocName4 As String

stDocName4 = "Append_LabourHours"
DoCmd.OpenQuery stDocName4, acNormal, acEdit


Dim stDocName2 As String

stDocName2 = "Delete_MOF"
DoCmd.OpenQuery stDocName2, acNormal, acEdit

Dim stDocName5 As String

stDocName5 = "Append_MOF"
DoCmd.OpenQuery stDocName5, acNormal, acEdit


Dim stDocName3 As String

stDocName3 = "Delete_POR"
DoCmd.OpenQuery stDocName3, acNormal, acEdit


Dim stDocName6 As String

stDocName6 = "Append_POR"
DoCmd.OpenQuery stDocName6, acNormal, acEdit

Dim stDocName7 As String

stDocName7 = "Delete_MaterialList"
DoCmd.OpenQuery stDocName7, acNormal, acEdit


Dim stDocName8 As String

stDocName8 = "Append_MaterialList"
DoCmd.OpenQuery stDocName8, acNormal, acEdit
----

This all works fine, but I would like to have a form open when the above operations have completed to list the amount of new entries were added for each table. i.e

2 entries for added under Labour Hours
4 entries for added under MOF
etc etc....

Can anyone point me in the right direction? I can add up the amount of entries, but not sure how I get the number of previous entries before deletion....if that makes sense!

Thanks in advance.
Frank.

hi,

Im pretty sure this is possible in access as ive found some examples but have been unable to incorporate them into my module.

Basically i need to set strNextValue as the number of tables in my access DB. Does anyone know of a function which counts the tables?

thanks.

Hi,

I have searched for an answer to this question a lot, but I could not find it. I hope anyone can help me with the following:

I need to do a database in MS Access; however, there is so much information and some of it is not related. After normalising it, it looks like I need a number of tables. Is there a limit to how many tables you can have on Access? And how many tables is the recommended size?

Any help will be very much appreciated.
Bee

I have a simple database with a data access page. End users simply select "New record" on the data access page, enter their first and last name and then select the time they want to attend a meeting from a drop down list consisting of three available time ranges (10:00-11:00, 12:00-01:00, 2:00-3:00)
I would like to limit the number of people who can select a certain time range to 75. For example if 75 people choose to attend the meeting at 10:00-11:00 then the 76th person who tries to select the same time should get a message stating "Meeting full, please select a different time"
Is this possible?
Thanks

Hi

Is there a way of counting the records in a number of tables and then inserting the table names and counts into a table. I have a table with the names of all the tables in it as this is populated when I import from excel.

The resulting table would be

Name Count
Table1 25
Table2 35

etc

I am using Access 2010

Thanks for your help

Rich

Hey guys.
Seems simple enough, I just can't figure out how to do it. How can I display the total number of table entries in a textbox on a form? thanks!

What I have is a business that bids about 400 jobs every year. A bid is done with a spreadsheet I have created that is a single page with about 10-12 columns dictating all the line items and charges etc. that it would take to accomplish a single job. We are awarded roughly 100 jobs per year. Each job is represented by a spreadsheet, and I need to get the data on each spreadsheet into an access database. This would be easy if each spreadsheet was static, but each spreadsheet changes as the job is actually worked, so I can't just append each Excel sheet into one large table and work with that. What I'm currently doing is making a table for every spreadsheet that reads the changes that occur in the spreadsheet and updates the database accordingly.

The one thing I have going for me here is that all the column names for each spreadsheet will always stay the same, it's only the rows of data that change. However, it gets increasingly difficult to manage queries that join and query 50-100+ tables at a time.

Can anybody either suggest a way to write a query that automatically combines a dynamic number of tables into one, or can you suggest a better way of designing my database? Is there some feature I'm not aware of that will let me handle/query so many different tables of data?

Hey

I'm trying to create a Library database. This involves restricting the number of loans available to each member (currently 3). I need to make it so no more than 3 records exist in that table for each member. For now ive just entered a VB script that has a message box saying not to enter more than 3 loans per person but I dont think thatll be enough, I need some sort of validation for the whole table rather than a field.

I know how to limit the returns of sql queries which is all I can find, but not how to limit the actual amount of entries.

The loan table is part of a double query within a subform of the member form if it helps :s

Hi. Here's an easy one for you (that I can't seem to figure out).

I've got a table with 1,000 records. One text field is SCHOOL NAME. I want to count how many records there are of each school.

For instance, there may be several records that have Lincoln County High School, several more that are Lincoln County Middle School, and several more that are West Elementary School, etc. There may be 70 or 80 different schools out of these 1,000 records and I need the total number of each school.

How do I have Access count the number of records with a specific entry within one field? Thanks.

Hi All,

I am wanting to build a table which consists of all the queries and tables in the database.

I am expecting column 1 to have all the queries and column 2 to contain all the tables that make up the query. So the table will look like this:

Queries - Linked Tables
Query1 - Table1
Query2 - Table1
Query2 - Table2
Query3 - Table1
Query3 - Table4

As you can see a table can be linked to any number of tables.

Now for my actual question. I don't want to have to type in all the queries and tables. I would like to choose any query and table from a drop down of all possible queries and tables that are currently in the database - like a list, where the list contains all the possible values.

I would then select the relevant entry.

Any pointers would be great.

Thanks,

Evan

Hi,

I've have seen an access database in which a field/box ask's for the number of individuals required for an event. The User, through either a list/combo box or direct entry, enters the number. As soon as user tabs out of box, a subform appears with the same number of empty lines ready for input of the pertinent information (name, age, address, etc.). For example, if the user enters '5', a subform would appear with five blank lines ready for the pertinent information to be entered. Once done, User would click on a button to save this info in a table, each line as an individual record. This program was a third party creation so it was considered proprietary and I couldn't see the code.

I wish to duplicate this process but am having a hard time of it. Any suggestions on how to reproduce this would greatly be appreciated.

Thanks

Hello there.

Is it better to have many fields in one table or to split the data between a number of tables and link them?

Thanks.

# gingette

Hello! I have a field named "KQs" in the table "Tracker" that looks like this:

KQ-12; KQ-2
KQ-2
KQ-14; KQ-12; KQ-5

Is there a way to strutcure a query so that I can find the number of times a particular KQ was entered into the table? For example, I'd like the query result to show: KQ-12 = 2.

Hello,
My "customer" form is based on sigle table. I have to restrict no of customers only 5 customers. can anybody help me ?
This is required for distributing a database.

Regards,

Satish

dear all,

i have a databse with a relatively large number of tables, queries, forms and reports as well as a significant amount of vba code and expressions. Now, the name of a number of the tables, queries, forms and reports has to be changed. However, the references to those in the queries, forms, reports, vba code and expressions should also be changed accordingly, otherwise the database does not work anymore. Does anyone know if change in the references can be done automatically or relatively easy without having to go through the entire database to check and change each and every reference/item.

many thanks in advance for the help.

best regards,

Hi,

We currently have all our work rosters in Excel and are looking at the possibility of moving them over to Access but am really not sure how to start with the design of the tables.Have been looking everywhere but cannot really find anything that will help me get started. The over view is we are in a 24 hr operation with over 1000 employees that can be broken down into 25 departments. We have the following types of rosters (days) :

On Off Start Types
6 4 Early's & Lates
4 2 Early's & Lates
5 3 Early's & Lates
4 4 Day's & Nights
6 4 Early's, Lates & Nights

Within each department there teams so say Dept1 would have Team1, Team2 and Team3 on a 6&4 Early's to Late Roster with the teams starting on different dates and Team's 4 & 5 doing 4&4 Day's and Nights also starting on different dates as well as part-timers and adhoc staff doing their own individual roster.

How can these rosters be stored in a number of tables?

hello all,, any help again,, would be greatly appreciated,,
i have a very simple database that has a number of tables that are all interelated by a key called JobCard,, a number of forms act as an interface to add to the data on the tables,,, what i would like to know is,, is there a method that allows the user to 'lock' the data in a filed once that filed or record is considered complete?
many thanks

We have an SQL server here at work that is used to hold customer transactions. Each customer has their own database. I had nothing to do with the design of the databases/server. It is an application provided by a vendor. Each new customer gets a new database with the same structure.

My task is to simply generate some better reports. There are reports for each customer, but there is nothing that totals all the customer transactions up to do company-wide reports. I know how to do a UNION query to combine two similar tables, but I don't know how to do that when the number of tables may change over time, and the tables themselves come from different databases on the same server.

So, get a list of databases, then append the various tables into a single table to be totaled up. Any ideas or resources?

My records are observations of organisms and the time when they were seen (hh:nn:ss). I would like to group my records into whole minute intervals and summarize the number of unique (different) organisms per minute. Ultimately, I am trying to produce a graph that shows the number of minutes it takes until no more unique organism are observed. The purpose of this graph is determine the amount of sampling time required before all of the biodiversity present in the ecosystem has been observed. Thanks

Hi,

I setup a generic report just to count the number of table entries by a particular field. E.g. Count of sales by product.

I then hoped to open this report using the Docmd.OpentReport and specify further criteria (strCondition) for the report.

DoCmd.OpenReport strReportName, acViewPreview, , strCondition

This doesn't work - I think because I have got an aggregate function in the report which it is calling.

Is there any other way to achieve the same result? - i.e. have a generic report (which contains an aggregate function) and pass further criteria to this using openreport or otherwise???

Is it possible to achieve the following in Access:
1. Create a SQL Statement.
2. Run the SQL Statement and send the results to a Report or Microsoft Excel document without having to have a report or query created.

Any help would be greatly appreciated.
Thanks

I swear I used to know how to do this, but I've tried a few things and can't figure it out.

OK. I've got a table with a date function. When you enter a file, it automatically puts in the date of entry. What I want to do is total the number of entries by month. I'd like the finished query to read something like:

Month # entries
January 113
February 37
March 90

and so on. Right now, I've got a query with EntryMonth:Month([Date]) and TotalMe: [EntryMonth] and then "sum" selected under "Group By," but this isn't doing it. What am I missing?

I am using Access to monitor bird nesting activity. I would like to determine how many nests were active on each day in a 3-4 month window and determine which day the maximum number of nests were active simultaneously.

My table (tblNest_Details) has the following columns:
Nest_Number
Date_Found
Date_Inactive

My goal to have results like this so I can find the date with the most or make a graph of activity over time:

6/1/12 10 active
6/3/12 12 active
6/15/12 20 active
6/27/12 24 active
6/29/12 17 active
7/5/12 8 active

The nests all have different found and inactive dates. Some nest become inactive before others are even found. I am currently working with a data set of approximately 100 individual nests.

Is it possible to contruct a query with the results I desire using just the Date_found and date_inactive columns?

Since I also have a separate table (tblNest_monitoring) that lists the day each nest was checked (while it was active between the Date_found and Date_inactive dates), I considered doing a query similiar to what one might do to determine daily student attendance. However the nest are not checked every day and different nests are checked on different days, so it is not quite like a student attendance record where each day would have an entry. I do not want to add entries for days nests were NOT checked because eventually I will be tracking over 800 nests in this database.

Thank you for your assistance.

I have a record that is getting data from a qry but I also want to display the total number of records from the table too. I tried this:

=Count([tblBnrStudent]![BnrID])

but it keeps asking me for the value of tblBnrStudent!BnrID.

I also tried using this:

=DCount([BnrID],"tblBnrStudent",???)

I wasn't sure what to put in the ??? if it wasn't null.

Hi, I have found various threads and such like regarding this. However, none of them make a great deal of sense to me. Could someone please explain to me how I would specify the number of records to be displayed on a single page. A way without code (or very little code) is prefered if possible but I can use code if absolutely necessary. It is for a school question and I just feel like there has to be a way without programming as it specifically says in the syllabus that no programming ability is expected... but anyway, any help would be appreciated. I am using a query bringing in fields from different tables and basing the report on this query. I want to have only four entries per page.

Thanks Hugely in advance

Jason


Not finding an answer? Try a Google search.