John-
You left out the COUNTER keyword.
Alter TABLE tbl_Manifest Alter Column ManifestNumber COUNTER (1000,1)
John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)
-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of yshopper
Sent: Tuesday, November 22, 2011 11:42 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Re: Database Design Help
I tried using this
Alter TABLE tbl_Manifest Alter Column ManifestNumber(1000,1)
by opening a new Query in design view, closing the Show Table dialog box,
clicking Data Definition on the design tab, and then clicking Run. Doing this
gets me this error:
"Syntax Error in Field Definition"
Any ideas what I'm doing wrong?
Thanks for any assistance.
John
--- In MS_Access_Professionals@yahoogroups.com, "Clive" <zctek@...> wrote:
>
> Hi John,
>
> The command to set an initial value for a Table ID Field
> is a DDL Query(not a DAO command like I misremebered).
> The syntax is,
> Alter Table TableName Alter Column FieldName Counter(S,I);
> where S is the StartNumber and I is the increment.
> So for an eight-digit ID field starting at 10,000,000
> incrementing by 1 use,
> Alter Table Table1Copy Alter Column ID Counter(10000000,1);
>
> If you must have a random number then you could use VBA
> code with the Rnd() Function but if you want fixed number
> of digits then you have to discard shorter results and
> also check that a duplicate is not present. Access will
> raise an error if you try to use use a duplicate number
> so that's the easiest way to check for dupes by handling
> the error.
>
> Hope that helps.
>
> Regards, Clive.
>
> --- In MS_Access_Professionals@yahoogroups.com, "Clive" <zctek@> wrote:
> >
> > Hi John,
> >
> > Just noticed that you have not had a reply yet.
> >
> > Q1. "I want to generate a random number for each box
> > along with the Ship date to identify each box shipped,
> > but don't know exactly how to do that."
> >
> > A1. To generate a unique number you can use the Access
> > AutoNumber Data_Type. If you have a good reason to
> > want a random number then you can specify
> > New_Values = Random, but that will give negative as well
> > as positive numbers and not the same length - probably
> > not what you want!
> > If you want the numbers to be at least 8 digits then
> > you can use AutoNumber but issue the DAO command to set
> > the starting value to 10000000. Sorry, I can't remember
> > the syntax and I have to go now.
> >
> > Will send more tomorrow.
> >
> > Regards, Clive.
> >
> > --- In MS_Access_Professionals@yahoogroups.com, "yshopper" <jlinux@> wrote:
> > >
> > > Apologies for the long post. I'll be as succinct as possible. I am a rank
beginner.
> > >
> > > Designing a database to track shipments from a Library to a company that
digitizes print material. We will be uploading the digital files into a Content
Management system after we receive the files from the scanning company. Steps in
the process include;
> > > 1. Receiving a selection list from the Librarian
> > > 2. Recording the items to be shipped (each item is barcoded)
> > > 3. Creating a shipping manifest w/a unique ID number for each box shipped
> > > 4. Recording the Shipment information in the company's special spreadsheet
> > > 5. Tracking the shipment, including the Date shipped and Date received at
the scanning company, the Tracking number and shipping receipt number
> > > 6. Recording the date received back from the scanning company
> > > 7. Recording Return Status; Re-assembled, Checked-in, Re-shelved, Uploaded
> > >
> > > That's the first step in the whole process and does not include recording
all the digital files and the projects and collections they will eventually
reside in. Here's my current line of thinking:
> > >
> > > Tables:
> > > Selection (for items from selection list)
> > > Manifest (to create the shipping manifest?)
> > > Box Number (to record the box number of boxes; i.e. 1 of 4)
> > > Shipping (all items shipped and returned)
> > >
> > > First I have some questions about Relationships, so please bear with me :)
> > >
> > > Each Item will be on only one Manifest
> > > Each Manifest will have many Items
> > >
> > > Each Manifest can be in only one Box
> > > Each Box can have only one Manifest
> > >
> > > Each Shipment can have many Manifests
> > > Each Shipment can have many Boxes
> > >
> > > Each Manifest can be recorded in a Special spreadsheet
> > > Each Special Spreadsheet can have many Manifests
> > >
> > > I want to generate a random number for each box along with the Ship date
to identify each box shipped, but don't know exactly how to do that. Should
include data from the Selection, Manifest and Box Number tables so I know what
Items were in what box and when they were shipped. Also need to figure out how
to record what Items were in each specific box, i.e. box 1 of 4, etc.
> > >
> > > I will be entering all the data into a Form linked to the Selection table.
All I need for the printed Manifest is the Item ID and the Call Number from the
Selection table. How would I get the data from the Selection table into the
Manifest table so I can print a report? I was thinking about using an Append
query to update the Manifest table, but not sure if that's the way to go.
> > >
> > > Will Exporting the data into their Special spreadsheet work? I'll need to
use it multiple times.
> > >
> > > I posted the report from the Database Documenter in the files section
named DB Definition.pdf, if that helps any.
> > >
> > > I appreciate any assistance that comes my way.
> > >
> > > Thanks,
> > > John
> > >
> >
>
------------------------------------
Yahoo! Groups Links
Tidak ada komentar:
Posting Komentar