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


Sponsored Links:



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