Have Table Primary Key Equal The Record Number.

Feb 3, 2008

Hi
How can I make the primary key (participant #) in my table equal the record number on the Form? In other words, when I open the form, I need two things to always happen:
1. the primary key must equal the record number (which is displayed on the form)&
2. when I click "Add New Record" button, I want a new customer # automatically generated and in the correct order.

Is this possible? I've been struggling...

View Replies


ADVERTISEMENT

Getting Only The Last Date Per Record In A Primary Table

Jul 27, 2007

I need to build the following query:

I have 2 tables:

tblBusiness
tblInspection

tblShops have an ID, an adress, bussines name, owner name, etc
every Shop can be inspected many times. A inspection can be futile if the store is closed. If the store is open, the inspection is made, and as result with can have “with irregularities” “without irregularities”. Two or more inspections can be made the same day (if the n – 1 time the store was close).

So, here is the basic select:

SELECT tblBusiness.idBusiness, tblBusiness.BussinessName, tblInspection.InspectionDate, tblnspeccion.InspeccionTime, tblInspection.Open, tblInspection.Irregularity
FROM tblBusiness INNER JOIN tblInspection ON tblBusiness.idBusiness = tblInspection.idBusiness;

What I need is to get the LAST inspection where the bussines was open and with irregularies. One inspection per Business, but of all business. Ah, and if there ir a newer inspection but the business was close, it doesn´t count.

I tried “Group by MAX” with InspectionDate, but it just didn´t work. For example, it showed me two inspection of the same business, not “the last” of that bussiness (it was easy to filter the Open field and the Irregularity field).

I suspect that what I need to solve this is to use two or more SQL stament, two in one., something like that. But I´m in a hurry and I just can seem to get it right.

I hope you can help me with this. Thanks for reading anyway.

P.S.: the fact that there can be more than one inspection per day can be a bitch =(

but there is a "InspectionTime" that can save that". Mmm the problem is that one inspection can be made 1 july around 20:00, but the seccond on 2 july 01:00. But we store the two inspections in 1 july... so... that mens problem.

But that issue is not so important as the previous problem up there (well, in order to solve the seccond I need to solve the firts...)

PS2: I don´t know about the title...

View 2 Replies View Related

General :: Enter Number Equal To Or Over Ten Million - Getting Last Two Values Rounded

Feb 28, 2013

When I enter a number equal to or over ten million, the last two values get rounded and I don't want them to be. My field is setup as a single, standard, two decimals. When I tested it I typed in 123456789, what I got was 123,456,800.00

enter 9999999 get 9,999,999.00
enter 10000199 get 10,000,200.00
enter 10000001.75 get 10,000,000.00

Single is supposed to handle up to 10^38

I have checked my "Region and Language" settings for the OS (W7) and there is nothing in there about rounding or maximum number size. I would like to leave the data type at single for the space considerations, and because it should work as single.

View 8 Replies View Related

Changing Misspelled Record In Primary Key Of Table

Sep 27, 2011

I want to change misspelled records, but the misspells are in the primary key of my table. By changing the values of primary key fields, can I break table relationships? I'm not changing field names, just field values of a primary key field.

View 5 Replies View Related

Adding New Record To All Tables For 1 Table Primary Key Entry

Oct 20, 2006

Hi, I have 7 tables in my database and 1 form corresponding to all the fields in these tables (linked by a query by recordsource). My problem is that the form will only show a record if ALL 7 tables have manually had the primary key entered (not good when i have information that needs to be added at different times). How can I make it such that if I create a new record on the form that all primary key fields will be updated, and this record will be present every time i open my the form??

Cheers

Tania :o

View 2 Replies View Related

How To Make Particular Fields Of One Record Equal The Value Of Another

Nov 14, 2012

I have created my first two tables ever.

The first is ClientList, which contains typical contact and biographical information (name, address, citizenship, etc), and unique ClientID# for each client. The primary key for this table is the default Autonumber ID that comes with each new table.

The second table is WillInfo, which contains information specific to drafting the client's Will (e.g., spouse name, spouse address, spouse citizenship, similar data on beneficiaries, similar data on executors, etc). The primary key for this table is ClientID#.

I then created a One-to-One relationship between ClientList and WillInfo, binding by Client ID. All this appears to work.

My question arises because I have two clients who are married to each other, which means much of the spouse info I require for the WillInfo table in respect of these particular clients is already accurately recorded as client info in the Clientlist table. So for these specific clients (but not generally!), I want the spouse information in the WillInfo table (e.g., SpouseAddress, SpouseCitizenship for ClientID# 12.001) to EQUAL specific values provided in the ClientInfo table (i.e., ClientAddress, ClientCitizenship for ClientID# 12.002).

I read and understand this is the best approach, following the principle that data should not be entered twice, so as to increase efficiency and avoid mistakes and future problems.

My question is: How do I do this? In Excel, if the client info I wanted to replicate was in cells B4-B9, I would enter =B4, or =B5, or =B6 and so on in the cells for spouse info. What is the equivalent expression for replicating specific client info from a different table.

View 7 Replies View Related

Forms :: Automatically Add New Record If Field Is Equal To Certain Value?

May 26, 2014

A have an OrderDetail form where I add the articles to a specific order. My articles can be ordered with 4 different attributes and one of them cost more than the others. If they want the article that cost more, I would like access to add a new record with some information.

also:
4 different attributes to my articles. (A, B, C, D)
if ArticleAttribute = A then Null
if ArticleAttribute = B then Null
if ArticleAttribute = C then Null
if ArticleAttribute = D then add NewRecord with ArticleId = X

View 14 Replies View Related

Value In A Table Equal To The Sum Of Other Values

Dec 11, 2007

Hey.

Is there any way to make a field in a record automatically set it self so it's the sum of other values in the record

http://img412.imageshack.us/img412/1677/helphg9.jpg

That's an example what i want to do except i had to manually type in 25. Is there any way to get that so it does that automatically. I am happy to use forms or macros or anything as long as it occurs straight away after the data is inputted and not have to run something or open something up

Thanks in advance

View 5 Replies View Related

Set The Primary Key Start Number

Jan 14, 2005

Hi
How can you set a primary key number (autonumber) to start from a particular number - eg "10000"
I have invoices issued from an old accounts package - and am creating a new database to replace it, but do not want to duplicate old invoice numbers. I seem to remember in the past a way of setting this but cant remember - any help please ?
Thanks

View 1 Replies View Related

Primary Key To Field (ID Number)

Apr 9, 2013

I have new database in this database i make primary key to field (ID NUMBER)

I enter data in this database just copy and past

If I enter any ID Number which is already in database , this database is stock

How I avoid this problem and how I let this database to accept other ID Number which is not in database and ignore other?

View 1 Replies View Related

Primary Keys And Auto Number

Feb 8, 2008

Hi there.

I am creating a database and i have the layout I want. although I am having problems as i am using autonumber as the primary key in each of the tables. Is there are way to make this work as the data to be entered into this database does not have a natural key until they are 4 months into the process.

i have attached my relationships diagram.

if it is not a good idea to use an auto number as the primary key does anyone have any suggestions as to what i should use?

View 2 Replies View Related

Primary Key-Auto Number - Increment

Feb 12, 2008

Hello ALL,

I want to know how to reset (to start again from 1) my primary key field. As an example i have the "Customers" table with the field "customerID" as PK now i entered many values (20) then i deleted them but i want to start again and the "customerID" value starts with 21 i understand that but i want a fresh DB (i was testing things) and i want to start from 1 can i? how? i even deleted the relation between this table and the other table but no success!

Some help please?

THX in advance

View 4 Replies View Related

Customized Primary Key With Auto Number

Oct 6, 2004

Hello,
I am a beginner and know a little about access. I am trying to create a customized Primary Key with an auto number. I want it to look like this: VP001 ,next record, VP002 and so forth. When I enter a new record and want it to do this automatically. It could be VP1, then VP2, I don't need the extra zeros but it would be nice. I don't know how to do this. If someone could please help walk me through this. Remember I am a beginner with databases and need help.

I would appreciate it

Thanks
Biz

View 2 Replies View Related

Primary Key-Auto Number - Increment

Feb 13, 2008

Hello ALL,

I want to know how to reset (to start again from 1) my primary key field. As an example i have the "Customers" table with the field "customerID" as PK now i entered many values (20) then i deleted them but i want to start again and the "customerID" value starts with 21 i understand that but i want a fresh DB (i was testing things) and i want to start from 1 can i? how? i even deleted the relation between this table and the other table but no success!

Some help please?

THX in advance

View 2 Replies View Related

Change Primary Key To Auto Number?

Nov 19, 2011

Can I change the primary key currently set to my products description to an auto number? I'm trying to make things right, also does the foreign key get an auto number?

View 2 Replies View Related

Tables :: Text As Primary Key Vs Auto Number

Jun 19, 2013

When I first was making my database I used all auto numbers for PK's. But then I read an article or two saying that you really don't need to do that if you don't have to (actually, the article said "don't use autonumber if you want anyone to understand your database). I have a few tables (most employee experience type tables) where I just use the word as the PK and there is no auto number in the table at all.I continue to build my database?

View 14 Replies View Related

Tables :: Can Format Auto-number For A Primary Key

Jun 23, 2013

Is there a way I can format AutoNumber for a primary key, for example, have the first ID start as C001 then for the next entry Access will automatically go to C002, and so on and so forth?

View 14 Replies View Related

Records In Table By Field, Not Record Number?

Oct 5, 2005

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 Related

Limiting The Number Of Times A Record Can Be Entered Into A Table

Sep 21, 2004

I want to limit the numer of times a record can be entere into a table. After a record has been entered 3 times, I want an error message or validation rule to tell me it can't be done.

View 7 Replies View Related

Auto-populate Junction Table Based On Equal Fields In Different Tables?

Jun 24, 2014

Is there an easy way to auto-populate a Junction table [in access 2010] given the following two tables with a many-to-many relationship for Tasks? The two tables are

Table 1) tblTasks (TaskID (PK), Description), and

Table 2) tblMeasures (MeasureID (PK), Description, Tasks)

If JCTN table is JCTN_Tasks_Measures (TaskID, MeasureID), is there a way to populate when tblTasks(TaskID) == tblMeasures (Tasks)?

View 1 Replies View Related

Tables :: Add ID Number That Always Increase By 1each Time Add New Record To Table

Jan 23, 2013

How can I add an ID-number that always increases (by 1) each time I add a new record to a table (using a form)? I want to always use some info from the record that was added last to fill in a different form. The reason for my question is that I heard that the autonumber does not necessarily increase each time a new record is added...

View 1 Replies View Related

"Not Equal And Not Equal" Query Not Working

Apr 14, 2006

I am trying to run a query where one field has a Not Equal parameter AND another field has Not Equal parameter (for example, Product Code Not Equal "A" AND Client State Not Equal "NY)

The result of the above query are all records that have Product Code = "A" are missing from the result. I want some of the "A" Product Codes; I just want to exclude the ones where the Client state is NY.

The list of values for each field is too big to state it in a positive way (I would have to list all the other 28 product codes and all the other 49 states).

What am I doing wrong? Thank you in advance for any suggestions.

View 1 Replies View Related

Modules & VBA :: New Record Button With Prefilled Number Based On Selection On Another Table

Jan 6, 2015

I'm making a database that so I can log calibration information about equipment every year.I have 2 tables:

The Equipment details table
The Calibration record table

ID Number is shared between the 2 tables so a calibration record can be linked to its' details.

For Example (simplified sample data):

Equipment Table
ID Item Unit Type
104 Thermometer DegC PT100

Calibration Table
ID Cal Point 1 Test Equipment Unit Under Test Date
104 20 21 22 06/01/15

What I want is a button on a form that creates a new blank record in my Calibration record table with the ID number already entered based on what record I selected in a combo box linked to my equipment table. I really don't know where to start.

View 2 Replies View Related

General :: Copy Single Record In A Table And Paste It Predefined Number Of Times

Sep 25, 2012

I would like to be able to copy a single record in a table and then paste this record a pre-defined number of times 10,20,30 depending on requirements. The reason I need to do this is that I want to set up a number of identical records that can easily be amended into unique records. This will vastly reduce the amount of work and time spent entering records individually.

The only way that I have been able to achieve this is to copy and transfer the master record into Excel, and then copy the row and paste it into a range of cells. Copy the range of cells back from Excel and then paste this range back into the Access table.

This works but is long winded; what I would like to know is is there a simply way of achieving this.

View 3 Replies View Related

Tables :: Procedure To Restrict / Stop Auto Number Increment For Certain Number Of Record Count

Mar 16, 2014

I would like to know if there is any procedure to restrict/stop auto number increment for certain number of record count (say 50), then increment by 1 for next 50 records.

View 8 Replies View Related

Adding A Record With New Primary Key

Dec 19, 2011

I am creating a form to add records to my database. I have created a primary key that is not autonumber. When adding a new record, I want the form to put a primary key default value of the next number in sequence. How do I do this?

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved