How to auto increment a field by number Results

Need help to figure out how to auto increment one field based on the value in another field. What I have is a table with six fields. There is a foreign key assigned to each record and linked to another table. Four fields in this table are number fields indicating accordingly a number for a category, a number for a subcategory, a number for a subject for a subcategory and a number for an instruction for each subject of subcategory. The last field is the text field for each instruction. Numbers are required to be included in the report. Also, number of categories, subcategories, subjects and instructions vary by each record in the the table which this table is linked to. What I'm trying to accomplish is to have numbers in the subcategory to auto increment starting with "1" for each number in the category field, numbers for each subject field would auto increment starting with "1" for each number in the subcategory field, and numbers in the instructions field to auto increment for each number in the subject field starting, again, with "1". I also need these numbers to restart with "1" for each new record in another table.

Basically, the table would look like this:
cat subcat subj instruction
1------1-------1------1
1------1-------2------1
1------2-------1------1
2------1-------1------1
2------1-------2------1
3------1-------1------1
3------1-------2------1
1------1-------1------1 (if the record in another table is a new/next table)
....and so forth....

I have been looking for anything that could remotely help me. Every VBA code I've tried is either don't work the way I need it to work, other codes making the field to continue increment numbers if the number already exists in the other field.

I hope I'm clear enough to explain my problem.

Any help would be greatly appreciated, I've been trying to solve this for the past three weeks with no luck...
Thanks in advance.

I have a subform with a field called "Visit_Number" the subform has a related childfield to the masterform
OutreachID Master(autonumber)
OutreachID child.

I need to add a record to the subform that keeps the current OutreachID (master) this works ok.
BUT I need the Visit_Number field to increment by one if a new record is added in the subform. I cannot use an auto number on the subform table because there may be more then one instance on the subform of the MasterID.

Anyone got any idea how i can increment the Visit_Number by 1 every time a record is added in the subform.

john

Pat Hartman, Anyone,

I saw a previous post response from Pat Hartman which explained this a bit, but I'm still not tracking for some reason.

I want to model a report log system that is currently in use in my office. Presently, transcript reports are simply listed in a logbook (rptnumber, date, operator, etc...). There are three logbooks (Log A, B, C) which have a simple sequential numbering system. example: Log A: AC12345, AC12346, AC12347.

An operator may choose any of the three logbooks when adding a new transcript report and simply write in the next sequential logbook number.

I have a form called frmTranscriptDetails populated by a query qryTranscriptDetailsDE. The PK for the main table is TRNumber which is formatted in the same alphanumeric string from our log books (i.e AC12345).

This is what I'm trying to accomplish.

When an operator creates a new record, a form will pop up prompting him/her to choose one of the three log books. If the operator chooses Log A, and clicks the "newRecord" command button, a new record is created in the frmTranscriptDetails form that autoinserts the the next sequential number from Log A in the TRNumber field of frmTranscriptDetails.

Based on previous posts I understand that I should somehow use the DMAX function to get the TRNumber of the last record, but I'm not too clear exactly how. Any insight on this would be greatly appreciated.

hi.

how can i increment a value (number) in a text box placed in a form header every time i input a value into a field on form detail section?

in form header is text box "count"

in form detail is tex box "SN"

after i input value into "SN" i want to value in "count" to go up by 1

i guessing it has to be done if SN after update event but how i don't know

Hi All

How to assign value to a field based on the value of the same field in the previous record?
Autonumber has its limitations for my purpose. Because, after I start filling up a form, I some times cancel the form filling process, and it affects the continuous numbering.

I guess the issue can be resolved by using some thing like record count facility. i.e. current' record's field value = number of records till previous record + 1.

How to exactly codify or execute the above by vba coding.

The Field Name is "SerialNo.", and Table Name is "StudentData"


Thanks

Padhuka

Former FoxPro Programmer struggling with Access 2010
To find the simplest thing takes days to find among all the stupid stuff I cannot find how to take the last record in the table get the records value (numeric) which is a serial number in a SQL Query and add 1 to it and append the next record and put that value in it. In Foxpro it was simple:
go bottom
store empno +1 to mempno
append blank
replace empno with mempno

Done
I have been going in circles and because Access has auto numbering fields (I cannot use them, I have to do no harm to the other programmers data). I do not have the luxury of doing anything with the structure of the SQL Database I cannot find the answer I have answered no to the error message (Was this information helpful) so many times was this helpful my mouse is broken. I have tried writing macros but they are extreamly limited I am not that familiar with VB but I have to get my head around it.
Here is the code for a append query that almost works but I can not get the last record number field value so I can add 1 to it:
SQL Query:
INSERT INTO dbo_EmployeeCloseOut ( LocationNo, EmployeeCloseOutNo, CloseDate, RegisterNo )
SELECT 1 AS Expr3, [dbo_EmployeeCloseOut]![EmployeeCloseOutNo]+1 AS Expr1, Date()+Time() AS Expr2, [Drawer Rec].[Register No]
FROM [Drawer Rec] LEFT JOIN EmployeeCloseOut ON [Drawer Rec].[Register No] = dbo_EmployeeCloseOut.RegisterNo;
I do not have the ability to change the target database structure and I have to adhear to their numbering sequence. Usually there are four records in the drawer rec file to add to the employeecloseout file and I want to link the two by EmployeeCloseOutNo, so when I get the number I have to place it in both tables.
Thanks for looking at this.

I am trying to make a primary key field for an auction database in Access 2010 on Windows XP SP3. I want the ID to begin with "LA" followed by auto-generated numbers that increment by one (so, LA0001, LA002, LA003 and so on). It only needs to be 4 digits long - the number of items will never be any higher than that. AuctionOrderNo only needs to be 3 digits long. And the database will only be a single user (me). I have one table (called LiveAuction) that includes the following Field Names, Data Types & Descriptions:


Field Name Data Type Description ID (Primary Key) AutoNumber The assigned number of the live auction item. AuctionOrderNo Number The order number the item will appear in the bidding list. Description Text The description of the live auction item. ItemValue Currency The dollar value the live auction item is valued at. SaleAmt Currency The dollar value the live auction item is sold for.
How can I accomplish this please? I don't mind creating VBA to do so, but I'm not a programmer and would need my hand held through that process! Thanks in advance for your time and assistance!

~biz_kid1

I am currently working on an access inventory system that will track all of our items and their categories (IT materials, office equipment, Office Supplies, communication materials, etc.) by generating automated sequential numbers as new records are updated into the system.
For instance: the “Item ID” attributed to “Office Equipment” is 100, the “Item ID” attributed to “IT materials” is 300. The first chair that got purchased or donated to our organization will be automatically labeled 100-1 in the “Item ID” field when I select Office Equipment in the drop down combo box on the “Item Type” field. A month later we purchased a new couch as part of our furniture stock and as we enter it in the record and select “Office Equipment” in the Drop down combo box of the “Item Type” field, the “Item ID” auto-increments automatically and becomes 100-2 although there are other records (following the same process in between).

I have inserted all the information below since I don't know how to use the VBA data and adapt it to my system. I know this may be a lot to ask…but finalizing this would be very helpful in helping us making sure our resources are distributed and monitored well in order to provide good service to beneficiaries.
I have received some suggestions but I don’t know how to adapt them to my system since I am not fluent in VBA:
I have gotten a suggestion...but can use it because I don't know how to adapt his code to my system.
Please HELP!

Below are all the fields exactly in the same order they are positioned in a table I named "Asset Inventory System". However the only relevant ones for this task are the "Item ID" field for the identifier (example: 200-1, 300-4, 100-6) and the "Item Type" field for the categories of item (example: Office Equipment, Office Supplies, IT Material, etc.) :

Item ID- Item Type- Description- Make & Model- Serial #- Unit- Condition- Location or Personal Use- Department- Cost or Value in HTG/ USD- Currency- Total Cost or Value- Purchase #- Date received- Donor- Loss/ Theft- Comments

Below are the item categories in a drop down box in the "Item Type" field as well as their identifiers (and example of increments) assigned to them.

Office Equipment 100-1,2,3…etc.
Office Supplies 200-1,2,3…etc.
IT Material 300-1,2,3…etc.
Communication Material 400-1,2,3…etc.
Audio & Video Material 500-1,2,3…etc.
Medical Equipment 600-1,2,3…etc.
Medical Supplies 700-1,2,3…etc.
Construction Material 800-1,2,3…etc.
Rolling Stock 900-1,2,3…etc.

I have gotten a few suggestions while searching online...here is the primary table to start with:

IF EXISTS (
SELECT *
FROM dbo.sysobjects
WHERE id = OBJECT_ID(N'[DF_SequenceControls_NextAvailable]')
AND type = 'D'
)
BEGIN
ALTER TABLE [dbo].[SequenceControls]
DROP CONSTRAINT [DF_SequenceControls_NextAvailable]
END
go
/****** Object: Table [dbo].[SequenceControls] Script Date: 05/22/2012 08:51:32 ******/
IF EXISTS (
SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[SequenceControls]')
AND type in (N'U')
)
DROP TABLE [dbo].[SequenceControls]
go
/****** Object: Table [dbo].[SequenceControls] Script Date: 05/22/2012 08:51:32 ******/
SET ANSI_NULLS ON
go
SET QUOTED_IDENTIFIER ON
go
SET ANSI_PADDING ON
go
IF NOT EXISTS (
SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[SequenceControls]')
AND type in (N'U')
)
BEGIN
CREATE TABLE [dbo].[SequenceControls](
[SequenceCtrlID] [int] IDENTITY(1,1) NOT NULL,
[TableName] [varchar](30) NOT NULL,
[Category] [varchar](30) NULL,
[NextAvailable] [int] NOT NULL,
CONSTRAINT [PK_SequenceControls] PRIMARY KEY CLUSTERED
(
[SequenceCtrlID] ASC
) ON [PRIMARY]
END
go
SET ANSI_PADDING OFF
go
IF NOT EXISTS ( SELECT *
FROM ::fn_listextendedproperty( N'MS_Description' ,
N'SCHEMA',
N'dbo',
N'TABLE',
N'SequenceControls',
N'COLUMN',
N'SequenceCtrlID'
)
)
EXEC sys.sp_addextendedproperty @name=N'MS_Description',
@value=N'Unique ID for each row' ,
@level0type=N'SCHEMA',
@level0name=N'dbo',
@level1type=N'TABLE',
@level1name=N'SequenceControls',
@level2type=N'COLUMN',
@level2name=N'SequenceCtrlID'
go
IF NOT EXISTS ( SELECT *
FROM ::fn_listextendedproperty( N'MS_Description' ,
N'SCHEMA',
N'dbo',
N'TABLE',
N'SequenceControls',
N'COLUMN',
N'TableName'
)
)
EXEC sys.sp_addextendedproperty @name=N'MS_Description',
@value=N'Name of the Table in which the Sequence Number is to be controlled.' ,
@level0type=N'SCHEMA',
@level0name=N'dbo',
@level1type=N'TABLE',
@level1name=N'SequenceControls',
@level2type=N'COLUMN',
@level2name=N'TableName'
go
IF NOT EXISTS ( SELECT *
FROM ::fn_listextendedproperty( N'MS_Description' ,
N'SCHEMA',
N'dbo',
N'TABLE',
N'SequenceControls',
N'COLUMN',
N'Category'
)
)
EXEC sys.sp_addextendedproperty @name=N'MS_Description',
@value=N'Optional category to be used to allow control of multiple sequences within a table' ,
@level0type=N'SCHEMA',
@level0name=N'dbo',
@level1type=N'TABLE',
@level1name=N'SequenceControls',
@level2type=N'COLUMN',
@level2name=N'Category'
go
IF NOT EXISTS ( SELECT *
FROM ::fn_listextendedproperty( N'MS_Description' ,
N'SCHEMA',
N'dbo',
N'TABLE',
N'SequenceControls',
N'COLUMN',
N'NextAvailable'
)
)
EXEC sys.sp_addextendedproperty @name=N'MS_Description',
@value=N'The next available sequence number within a given Table/Category sequence' ,
@level0type=N'SCHEMA',
@level0name=N'dbo',
@level1type=N'TABLE',
@level1name=N'SequenceControls',
@level2type=N'COLUMN',
@level2name=N'NextAvailable'
go
IF NOT EXISTS ( SELECT *
FROM dbo.sysobjects
WHERE id = OBJECT_ID(N'[DF_SequenceControls_NextAvailable]')
AND type = 'D'
)
BEGIN
ALTER TABLE [dbo].[SequenceControls]
ADD CONSTRAINT [DF_SequenceControls_NextAvailable]
DEFAULT ((1))
FOR [NextAvailable]
END
go

and here is the Stored procedure to get the next available number in the sequence:

-- Author: Ralph D. Wilson II
-- Create date: 2012-05-22
-- Description: dbo.usp_TWG_Fetch_Next_Availble_Number
-- This procedure will accept a TableName
-- and a Category and return the lowest
-- NextAvailable value for that sequence.
--
-- If the sequence does not exist within
-- the SequenceControl table, it will initialize
-- the sequence. If there is only one row available
-- for that sequence, it will increment the
-- NextAvailable value and insert another row
-- with the new NextAvailable value.
--
-- MODIFICATION HISTORY:
-- Date Author Comment
-- ---------- ------ -------------------------
-- 2012-05-22 RDWII Initially Coded
-- =============================================
ALTER PROCEDURE dbo.usp_TWG_Fetch_Next_Availble_Number
@TableName Varchar(30) = ''
,@Category VarChar(30) = 'NONE'
,@NextAvailable INT OUTPUT
AS
BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON;

DECLARE @AvailableNumbers INT;

SELECT @AvailableNumbers = COUNT(1) FROM SequenceControls WHERE TableName = @TableName AND Category = @Category; IF (@AvailableNumbers = 0) BEGIN -- Since there are no rows whatsoever for this sequence -- we need to initialize the sequence, returning the -- first sequence number, and insert a second -- sequence number INSERT INTO SequenceControls ( TableName ,Category ,InUse ) Values ( @TableName ,@Category ,'Y' ); INSERT INTO SequenceControls ( TableName ,Category ,NextAvailable ) Values ( @TableName ,@Category ,2 );

SET @NextAvailable = 1; END ELSE BEGIN -- Since there ARE rows whatsoever for this sequence -- we need to try to get the NextAvailable sequence -- number

SELECT @AvailableNumbers = COUNT(1) FROM SequenceControls WHERE TableName = @TableName AND Category = @Category AND InUse = 'N'; SELECT @NextAvailable = NextAvailable FROM ( SELECT TOP 1 NextAvailable FROM SequenceControls WHERE TableName = @TableName AND Category = @Category AND InUse = 'N' ) Z ORDER BY NextAvailable; IF @AvailableNumbers = 0 BEGIN -- Oops, all of the sequence numbers are marked as InUse. -- That means that we need return a sequence number one -- greater than the highest one in the sequence and then -- insert the next one after that. SELECT @NextAvailable = MAX(NextAvailable) + 1 FROM SequenceControls WHERE TableName = @TableName AND Category = @Category;

PRINT 'Inserting a new available row for: ' + @TableName + '/' + @Category;

INSERT INTO SequenceControls ( TableName ,Category ,NextAvailable ) Values ( @TableName ,@Category ,@NextAvailable + 1 );

INSERT INTO SequenceControls ( TableName ,Category ,NextAvailable ,InUse ) Values ( @TableName ,@Category ,@NextAvailable ,'Y' );

END ELSE BEGIN -- Okay, we got one. Now, we need to see if there was only one available.

IF @AvailableNumbers = 1 BEGIN -- Yup, only one was available, so we need to flag that one as InUse -- and insert another row with the next higher NextAvailable value. UPDATE SequenceControls SET InUse = 'Y' WHERE TableName = @TableName AND Category = @Category AND NextAvailable = @NextAvailable; INSERT INTO SequenceControls ( TableName ,Category ,NextAvailable ) SELECT TableName ,Category ,MAX(NextAvailable) + 1 AS NextAvailable FROM SequenceControls WHERE TableName = @TableName AND Category = @Category AND NextAvailable = @NextAvailable GROUP BY TableName ,Category; END BEGIN -- Nope, more than one was available, so we just need to flag -- that one as InUse. UPDATE SequenceControls SET InUse = 'Y' WHERE TableName = @TableName AND Category = @Category AND NextAvailable = @NextAvailable; END END END END Here is the Stored procedure to release a previosuly selected (but not used number)in the sequence.-- Author: Ralph D. Wilson II-- Create date: 2012-05-22-- Description: dbo.usp_TWG_Release_Sequence_Number-- This procedure releases a sequence -- number that was previously marked -- as InUse.---- MODIFICATION HISTORY:-- Date Author Comment-- ---------- ------ --------------------------- 2012-05-22 Initials Initially Coded-- =============================================ALTER PROCEDURE dbo.usp_TWG_Release_Sequence_Number @TableName Varchar(30) = '' ,@Category VarChar(30) = 'NONE' ,@SequenceNumberToRelease INTASBEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; UPDATE SequenceControls SET InUse = 'N' WHERE TableName = @TableName AND Category = @Category AND NextAvailable = @SequenceNumberToRelease; END Author: Ralph D. Wilson II-- Create date: 2012-05-22-- Description: dbo.usp_TWG_Consume_Sequence_Number-- This procedure consumes a sequence -- number that was previously marked -- as InUse.---- MODIFICATION HISTORY:-- Date Author Comment-- ---------- ------ --------------------------- 2012-05-22 Initials Initially Coded-- =============================================ALTER PROCEDURE dbo.usp_TWG_Consume_Sequence_Number @TableName Varchar(30) = '' ,@Category VarChar(30) = 'NONE' ,@SequenceNumberToRelease INTASBEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; DELETE FROM SequenceControls WHERE TableName = @TableName AND Category = @Category AND NextAvailable = @SequenceNumberToRelease AND InUse = 'Y'; END Author: Ralph D. Wilson II-- Create date: 2012-05-22-- Description: dbo.usp_TWG_Consume_Sequence_Number-- This procedure consumes a sequence -- number that was previously marked -- as InUse.---- MODIFICATION HISTORY:-- Date Author Comment-- ---------- ------ --------------------------- 2012-05-22 Initials Initially Coded-- =============================================ALTER PROCEDURE dbo.usp_TWG_Consume_Sequence_Number @TableName Varchar(30) = '' ,@Category VarChar(30) = 'NONE' ,@SequenceNumberToRelease INTASBEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; DELETE FROM SequenceControls WHERE TableName = @TableName AND Category = @Category AND NextAvailable = @SequenceNumberToRelease AND InUse = 'Y'; ENDStored Procedure to remove (consume) a previously selected sequence number (Note: if an unselected number is specified, nothing happens): Author: Ralph D. Wilson II-- Create date: 2012-05-22-- Description: dbo.usp_TWG_Consume_Sequence_Number-- This procedure consumes a sequence -- number that was previously marked -- as InUse.---- MODIFICATION HISTORY:-- Date Author Comment-- ---------- ------ --------------------------- 2012-05-22 Initials Initially Coded-- =============================================ALTER PROCEDURE dbo.usp_TWG_Consume_Sequence_Number @TableName Varchar(30) = '' ,@Category VarChar(30) = 'NONE' ,@SequenceNumberToRelease INTASBEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; DELETE FROM SequenceControls WHERE TableName = @TableName AND Category = @Category AND NextAvailable = @SequenceNumberToRelease AND InUse = 'Y'; END


This is all! i would be very very very greatful if someone could help! thank you again!!!

Is it at all possible to increment a number in a table depending upon the number in the row previous.

Eg
Field1
C1
C2
C3
C4

I know it is possible as the auto number field set by access when no primary key is defined carries a similar feature out. I have looked at the field properties for the autonumber and an option of increment is available in a new value section however for any fields that i create I can not get this option to appear.

I have tried to use the default value to try and increment the cell previous but im not too sure to do this either.I can dio basic sums bu im not too sure how to increment a number or how to reference other cells. Im not too sure if this is possible tho.

Has anybody got any idea how to do this. Is there a way of doing this via VBE? I am familiar with loops but am not too sure how to manipulate tables in access using code.



Thanks for your time

Chris

Hi all,

I'm just having a dabble in VBA for Access so I'm fairly newbish!

I have a field [Reference Number] that increments in the table by 1 everytime - but the user has to enter this manually (its not auto-incrementing). How can I do the following: when the user selects a new row in the table it will automatically look in the table for the last entered [Reference Number], increment it, and put that value in the Userforms TextBox??

Hope you understand this,

TIA

I have a simple database to record daily purchases of newspapers for a hotel. I have 2 tables, one with the description and current price of the paper Papers, the other Daily Purchase has the daily purchase quantity and date of purchase along with the current price. I want the form to use the current price by default from the Papers database for new entries. I want to ensure that if the price is changed in the papers table, new entries will show this in daily purchases. How do I make sure the current price is correct in daily purchases?

Papers

Paper ID: (auto increment, primary key)
Description: Text
Price: Currency

Daily Purchase

Purchase ID: (auto increment, primary key)
Paper ID: Long Integer
Qty: Number
Price: Currency
Date: Date/Time

Hello all .
I have a table where I would like the primary key to be automatically generated by Microsoft Access. At present I have the field set to Auto Number(Random) and Long Integer. Unfortunately this gives me both positive and negative values. How can I get Access to just return positive numbers (I do not wish for the number to be incremented - I need random.)


Thanks for reading and I hope you can help!

Hi,
I have a form with a subform. I don't want to use autonumber for my [ReferralNumber] field because I don't want the records to start at 1. But I would like to have the field autopopulated with a number each time I open a new form. So if I decided to have the referral number begin at 100 and increment by 1 with each new form that opens how would that look?
Thanks,
Lynn

I would like to use a control button and an UnBound field on a form to obtain a unique record number in Access 2010 starting with 00001. The number will be incremented by 1 each time the control button is selected. The difficulty is on Oct 1st of every year the record count has to auto reset and start over at 00001. As the record number increases the preceding zeros should decrease to maintain four digits or characters, e.g, 00193. Thus far, nothing that I have tried in vba has worked. Any ideas how this might be accomplished? Any ideas or help will be much appreciated.


Thanks

My database consists of contracts. Each contract has a specific type or "category" (there are about 10 categories). The user will be given a contract infront of them, they can tell the type of contract by looking at it...therefore, I made a category list for the user to select from. Each category corresponds with a given contract number on the document itself (therefore, a user will manually enter that field in). So far...so good. The problem is (and this is the special case), there is a contract that comes in, the "MU" category, where there is no contract number on it, therefore, the user cant manually enter because they don't have it. What needs to be done, which was accomplished (i showed you the previous coding i had), was when MU was selected, it would auto generate a contract number incrementing the last "MU" contract number by 1. So no matter how many contracts were in between, the last 2 MU contracts should only be seperate by one number.

So that's what I had, and that code did that.

After talking to my boss, I realized I needed to make a couple altercations.

The first one being the format to show up as 0002, 0003...and so on...which we accomplished as well.

The last thing we needed was the letters "MU" to show up in front of the contract number, MU0002, MU0003..and so on..but the problem is it wont increment another number because the text is having somethin to do with it.

When trying your suggestion, the MU category worked perfect, right format, had the "MU", and incremented by 1, but all of the other categories (once selected), were autogenerating the next MU number...so that's where we are. If you have any further questions or want to see my DB...let me know, but I think I gave you everything..but who knows.

So let me know what you think...and thanks for your patience.

-Josh

My problem envolved into coding so I have threads elsewhere in forms...the first one contains the current code i have, the 2nd was the beginning and more backround info....

http://www.access-programmers.co.uk/...d.php?t=120881

http://www.access-programmers.co.uk/...404#post550404

Warning - Complete newbie post!

Hi all,

I have 2 Access databases setup, each containing 1 table. Let's call them A and B. These two tables contain some of the same fields.

Table A is used for collecting new client registration info through our website and gets updated by SQL.

Table B is on a different server and is used for company newsletter mailings.

Table A is the one which is automatically being updated so I don't need to do anything with that table. Also, some fields in Table A are auto-incrementing numbers so we can differentiate a clients interest and provide a more informative newsletter for them specifically on their selected interest.

Whenever an entry in certain fields of Table A is made, I would like the same fields of Table B to be updated with that same information automatically. For example...

Client subscribes to our newsletter on our site and selects their interests, the email field now contains the subscribers email address and the various 'interest' fields contain a '1' telling us they are interested in that particular area of our business. As this takes place on Table A, it needs to be also replicated to Table B.

Surely this is possible, but how?

I am creating a database for a mining company. There are trucks carrying materials from one place to another. In the database there is the table Trips with the following fields:

Record_ID
Date_ID
Time_ID
Truck_ID
Trip_Count

Each time a truck loads materials a record is created specyfying the record number (Record_ID, auto increment) the current date (Date_ID), the time the truck started loading (Time_ID), the ID of the truck (Truck_ID) and a variable (Number, Trip_Count) which value is usually "1" but at times it can have different values as well. Then another truck comes in, another record is created and then another etc.

Here's how the table looks like:

Record_ID | Date_ID | Time_ID | Truck_ID | Trip_Count
___ 1 ___ | 2/5/08_ | _7:49__ | __ 3 ___ | ___ 1 ____
___ 2 ___ | 2/5/08_ | _7:55__ | __ 4 ___ | ___ 1 ____
___ 3 ___ | 2/5/08_ | _7:57__ | __ 5 ___ | ___ 1 ____
___ 4 ___ | 2/5/08_ | _7:59__ | __ 3 ___ | ___ 1 ____
___ 5 ___ | 2/5/08_ | _8:01__ | __ 4 ___ | ___ 1 ____
___ 6 ___ | 2/5/08_ | _8:02__ | __ 6 ___ | ___ 7 ____
___ 7 ___ | 2/5/08_ | _8:03__ | __ 5 ___ | ___ 1 ____
___ 8 ___ | 2/5/08_ | _8:07__ | __ 3 ___ | ___ 1 ____
___ 9 ___ | 2/5/08_ | _8:10__ | __ 3 ___ | ___ 1 ____
___ 10 __ | 2/5/08_ | _8:16__ | __ 3 ___ | ___ 1 ____
___ 11 __ | 2/5/08_ | _8:20__ | __ 3 ___ | ___ 1 ____
___ 12 __ | 2/5/08_ | _8:24__ | __ 3 ___ | ___ 7 ____
___ 13 __ | 2/5/08_ | _8:27__ | __ 3 ___ | ___ 4 ____

By the end of the day the database table is filled with the information of all the trucks who worked that day along with the time they performed each load. This happens every day.

What I want to do and I don't know whether it is possible with Microsoft Access, is to calculate the interval between two records of the same truck.

For instance, the first interval for the truck with Truck_ID 3 would be 10 minutes in the example above. The interval for truck with Truck_ID 4 would be 6 minutes. The interval for truck with Truck_ID 5 would be 6 minutes. I want to gather all the intervals for a truck and find an average time for each day.

Let's say I want to see all the records for the truck with Truck_ID 3 at 2/5/08 who have a Trip_Count value of 1.
So I create a query using the following SQL code:


	Code:
	SELECT
Trips.Truck_ID,
Trips.Date_ID,
Trips,Time_ID,
Trips,Trip_Count
FROM Trips
WHERE (((Trips.Truck_ID)=3) AND ((Trips.Date_ID)=#2/5/08#) AND ((Trips.Trip_Count)=1));

Here are the results of the query:

Date_ID | Time_ID | Truck_ID | Trip_Count
2/5/08_ | _7:49__ | __ 3 ___ | ___ 1 ____
2/5/08_ | _7:59__ | __ 3 ___ | ___ 1 ____
2/5/08_ | _8:07__ | __ 3 ___ | ___ 1 ____
2/5/08_ | _8:10__ | __ 3 ___ | ___ 1 ____
2/5/08_ | _8:16__ | __ 3 ___ | ___ 1 ____
2/5/08_ | _8:20__ | __ 3 ___ | ___ 1 ____

In order to calculate the time interval between those 6 records I create a query with the following code:


	Code:
	SELECT
A.Truck_ID,
A.Date_ID,
A.Time_ID,
Min(B.Time_ID) AS NextTime,
A.Trip_Count,

FROM Trips AS A LEFT JOIN Trips AS B ON
(A.Truck_ID = B.Truck_ID) AND
(A.Date_ID = B.Date_ID) AND
(A.Time_ID < B.Time_ID)

GROUP BY A.Truck_ID, A.Date_ID, A.Time_ID, A.Trip_Count

HAVING (((A.Truck_ID)=3) AND ((A.Date_ID)=#2/5/08#) AND ((Trip_Count)=1));

And here are the results of this query:

Date_ID | Time_ID | NextTime |Truck_ID | Trip_Count
2/5/08_ | _7:49__ | __7:59__ | __ 3 __ | ___ 1 ____
2/5/08_ | _7:59__ | __8:07__ | __ 3 __ | ___ 1 ____
2/5/08_ | _8:07__ | __8:10__ | __ 3 __ | ___ 1 ____
2/5/08_ | _8:10__ | __8:16__ | __ 3 __ | ___ 1 ____
2/5/08_ | _8:16__ | __8:20__ | __ 3 __ | ___ 1 ____
2/5/08_ | _8:20__ | __8:24__ | __ 3 __ | ___ 1 ____

All I have to do now is subtract the Time_ID field value from the NextTime field value.

Question 1: How do I calculate the interval between the two fields? I try to subtract one field from another using the DateDiff function but it produces really weird results. For example I use the following code:


	Code:
	SELECT
A.Truck_ID,
A.Date_ID,
A.Time_ID,
Min(B.Time_ID) AS NextTime,
A.Trip_Count,
DateDiff("n",[A].[Time_ID],[b].[Time_ID]) AS TimeInterval

FROM Trips AS A LEFT JOIN Trips AS B ON
(A.Truck_ID = B.Truck_ID) AND
(A.Date_ID = B.Date_ID) AND
(A.Time_ID < B.Time_ID)

GROUP BY A.Truck_ID, A.Date_ID, A.Time_ID, A.Trip_Count, DateDiff("n",[A].[Time_ID],[b].[Time_ID]) AS TimeInterval

HAVING (((A.Truck_ID)=3) AND ((A.Date_ID)=#2/5/08#) AND ((Trip_Count)=1));

Question 2: Why does the time value 8:24 appears in the query results (in the final record in the NextTime field) since this value belongs to a record with Trip_Count 7?

Question 3: Can you recommend another method of approaching this issue of calculating the time interval between records of the same table? Can you recommend a different method of calculating the intervals and finding an average?

Well, that's all! I hope you can help!

Greetings,

I am trying to myltiply two fields on my continuous form, I have a formula that i put in unbound textbox and works fine, but just for the first row, the next one in a line gives the same value of the first row even that is not correct:
I hope im explaining myself what im trying to acomplishe here.
please find bellow some more informations that will help understending my needs. I really appreciate any help I can get
---
my dba:

Table: itemtbl
ID - AutoNumber/Long Integer/Increment (default created by access 2007)
Item - Text/255
Price - Currency/Euro/Auto
Qty - Number/Single
Total - Currency/Euro/Auto

Continuous Form: fields,
name: Itemtxt
selection type: Text Box
Control Source: Item
---
name: Qtytxt
selection type: Text Box
Control Source: Qty
---
name: Pricetxt
selection type: Text Box
Control Source: Price
---
name: totaltxt
selection type: Text Box
Control Source: Unbound
--------
I have 3 records on table called itemtbl
(this is how its diplayed on my continuous form)
ID Item Qty Price Total
1 Book 1 2.50
2 Pencil 3 1.40
3 TV 2 99.90
------
Agan how to multiply qty with Price and get a sepret value for each line/record/row on my total filed

Kind Regards

I have many varied lists of citizens from our town from 1700s to present day. Some have come in Excel, or Lotus format, most are docs. At this point I have been able to parse everything into Excel, however I now have about 60 Fields because of the variety of data garnered from each list. Over the past year I have used formulae to parse the data, usually from a "Notes" field that includes just about anything from genealogy, military, occupational, historical articles and artifacts, to the condition of the gravestone, etc. Obviously, aside from names, birth and death dates, parents names, most of the other data is sparse, sometimes only 50 individuals out of 3000+. At this point, my ignorance about what one can do in Access has kept me from making the changeover, but I need to do something with what I have because it is now far too unwieldy in Excel. Worse yet, I was just informed that another batch of a couple thousand records has been found, and I will probably have to repeat the process with all the new records later.

I have a meager understanding of Access, but am trying to read about it in my spare time. So I have some generalized questions about what Access can do, as it will affect how I next manipulate the data in Excel. I appreciate that your time is valuable, so I’m not asking you how to do this as much as asking you if it is possible to do it in Access. I would appreciate it if you only say “No that is not possible/the right approach/etc” or if you suggested a method of attack by saying “look up ‘how to add a primary key’ or ‘use the ** wizard’”, and I will have a hint of how to attack the problem. Thanks muchly for your help and your patience.

As I have many redundancies in individuals (a parent may have had six children for example), I have sorted all names alphabetically, and then assigned a number to each row. I then repeated the number column and deleted the number from the repeats (i.e. Lee 1, Lee 2, Leed 3 becomes 1,3) and can then sort for unique individuals. I then imported the names into Access and got a primary key for each unique individual, then put that key back into Excel and copied the key to all the Lee 2s that had no key so that all listings have a primary key. Now what is the best way to get all the rest of the data into Excel? (These questions are not necessarily in any order of execution.)

1. I understand that I can have Access take the Excel file in its entirety and make a db out of it.
· What will happen to the Primary Key I have already assigned?
· Can I then import another spreadsheet to the same db from the new files I will have?
· Or should I make a second db from the new files and merge 1st and 2nd together?
· And finally, must all Excel files have all 60 field columns (in the case of the docs, there is generally only names and one or two other items, such as registered voter [yes/no] and nothing else)?

2. As there are a number of repeats of names and so many relationships, I have assigned a Personal ID (not the Access primary key) in Excel to each person so that I could list the parent’s PID with the children and vice-versa, as well as husband/wife, etc. (There are 6 John Smiths with a total of 17 children.) And finally, there are many names of people from other places who will not need to be in the db for the town (the names of parents of a woman who married a man from our town, for example).
· Can Access search for those extra names if they are only noted in the Father/Mother fields, or should they be listed as Lastname, Firstname and given a primary key of their own?
· Though I think it might end up being redundant in the db once I have a relationship table set up, should I keep the PID with each individual? (The problem is that unlike a primary key, my key has numbers and letters [PID123], and I’m not sure if it is possible to format an auto-incrementing number.)

3. I noticed that in the forms samples in Access, one can use a number of tabs, or macros to switch to different forms. As I am hoping that this db will outlast me, and as most of the data entry will be done by those with little or no experience in db construction, I want to keep this db as simple as possible. Since tabs seems very simple to use, is there any advantage of using the macro buttons vs tabs?

4. There are a number of dates (birth, death, marriage, enlistment, discharge, etc) but they are not all “related” in the sense that enlistment and discharge could be in a military table and birth and death on a personal info table and marriage on a relationships’ table, so would these dates be better normalized or just left as items in different tables? (The issue is a memory one in that few have enlistment/discharge dates, and some have only death date, etc.)

I think that is enough for this forum thread. Once again, thank you for your time and patience.

Hello,

I am teaching myself Access after years of working with Lotus Approach. I am creating a Customer database in access after importing tables from Approach. I have a table called "Customers" that had existing data and have set the primary key to "customerID" which is the same field that was used in Approach.

This customerID field needs to auto number and increment by 1 each time a new customer record is entered, but when I try to change the field to autonumber, I get this message: "Once you enter data in a table, you can't change the data type of any field to autoNumber..."

I really don't want to recreate thousands of customer records because the customerID is used in relationships with other tables as well.

How can I use my existing ID field and get it to autonumber when a new record is added?

Thanks very much,

Steve


Not finding an answer? Try a Google search.