If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Highest Value Query
Hi.
I have a table, which has an autonumber as its primary key. Secondly, I have a field which is called AssetPlantID - this holds the unique Plant ID for each record stored, the ID is based apon the department code which is stored in the department table, and then a number, 1,2,3 etc in the table I have many records, i would like to increment this AssetPlant ID automaticly for each new asset added to the database - getting the DepartmentCode to appear before the ID is fine, its querying the previous IDs to find the last is the problem I am aware of the Dmax function, but this is no use with the Id having letters before the numbers for example A1, A2, A3, A4, B1, B2, B3 etc are all AssetPLant IDs How would I find the last number which is prefixed with an A with a query? |
#2
|
|||
|
|||
Highest Value Query
The problem is that your database structure violates the First Normal Form
(Data must be atomic) which specifies that each Field value must store only ONE item of data, not multiple items of data (i.e. a list) or a combined value. In your case, the AssetPlantID Field value actually store 2 items of data: the DeptCode and a SeqNo. It is much better that you restructure the Table to store the AssettPlantID in 2 different Fields. If you need to combine these 2 values for display, it is always easier to combine them than to extract 2 or more components from a single value. That's said ... but if your DeptCode is always, one character, then you can use: Val(Mid([AssetPlantCode], 2)) to obtain the SeqNo from the AseetPlantID. -- HTH Van T. Dinh MVP (Access) wrote in message ups.com... Hi. I have a table, which has an autonumber as its primary key. Secondly, I have a field which is called AssetPlantID - this holds the unique Plant ID for each record stored, the ID is based apon the department code which is stored in the department table, and then a number, 1,2,3 etc in the table I have many records, i would like to increment this AssetPlant ID automaticly for each new asset added to the database - getting the DepartmentCode to appear before the ID is fine, its querying the previous IDs to find the last is the problem I am aware of the Dmax function, but this is no use with the Id having letters before the numbers for example A1, A2, A3, A4, B1, B2, B3 etc are all AssetPLant IDs How would I find the last number which is prefixed with an A with a query? |
#3
|
|||
|
|||
Highest Value Query
thanks, i have thought of using the Val(mid) thing, but one or 2 of the
Dept Codes are 2 Chars! I am aware of the problem with the 1nf, the problem being, I am designing a new Relational database - and have to keep the data from the old database the same, esp AssetPlantID which was used as a primary key for an un-normalised table in the previous db I think i am going to restructure the table as you said, to hold the plant id in one field (numeric, and allow duplicates) then query the department first, then do dmax on that data in the query, which would give me the largest value of that department code! Thanks for your reply, weight of my head! Hope to use this Group more often! |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Error when running Query, but not Report | Blaze | Running & Setting Up Queries | 29 | September 9th, 2005 05:40 AM |
Moving from xBase/Clipper | [email protected] | New Users | 1 | February 3rd, 2005 07:25 PM |
adding 2 fields including null entries | Jesse | Running & Setting Up Queries | 26 | January 18th, 2005 05:31 PM |
Here's a shocker | Mike Labosh | General Discussion | 2 | October 26th, 2004 05:04 PM |
Too Few Parameters error Mail Merge Access Parameter Query | Tony_VBACoder | Mailmerge | 3 | September 14th, 2004 12:15 PM |