how to parse data from different tables to creat an auto generated number

[zimes]

I am working with Microsoft Access 2003 from the Office XP pro suite. I have created several tables that have to do with Asset tracking along with purchasing. I have a table called Purchasing, Assets, Comapnay, AssetCategorty, and AssetNumber. The number I would like to create would take data from Purchasing and Assets, then create a number that would be in this format:

YY| company 3 letter abrivation | Asset Type | 3 digit auto generated number
it should look like this
04ABCLTP001 or 04-ABC-LPT-001

For each table I have (unrealated data I have kept out):

Purchasing
PurchaseID {PK}
CompayPurchasedFor{FK to company}
DateRecieved

Assets
AssetID{PK}
AssetCategoryID{FK to AssetCategory}
AssetNum

AssetCategory
AssetCatID{PK}
AssetName

Company
CompanyID{PK}
Company
CompanyAbrv

AssetNumber
AssetNumberID{PK}
PurchasingID{FK to CompayPurchasedFor(The FK in Puchasing) and DateRecieved}
AssetID {FK to AssetCategoryID(FK to AssetCategory)}

The data parsed to PurchasingID into the AssetNumber Table needs to have the DateRecieved and CompanyPurchasedFor.
The data that goes to the AssetID in the AssetNumber Table comes from the AssetCategoryID in the Assets Table.

I then need to get that information to the AssetINum column in the Assets table and add the auto number.

the trouble is getting the finish product. the purchasingID in the AssetNumber is not showing the CompanyPurasdedFor information, just the date. I know I am missing something in the code since it is just comming in as the date and I dont know how to code it to show the last two digits in the year. I also want to show the CompanyAbrv instead of the full company name. here is the code:

SELECT PurchaseID, CompanyPurchasedFor, DatePurchased
FROM Purchasing
ORDER BY Purchasing.DatePurchased, Purchasing.CompanyPurchasedFor;

Q1) what am I missing in the above code to get the format of (YY/XXX)?

Q2) how would I code that information to point back to the AssetNum back in the Assets Table?


Tanks for the help in Advance

[nico5038]

For your needs you need to look into the Format() function to get just the "YY" format, but I would like to warn you for generating these type of codes.
Just imagine what happens when adding a combination of "YY| company 3 letter abrivation | Asset Type" and it's already occurring 999 times....
Or what to do when a company's abbreviation changes...

Best way is always to use a "hidden" auto-number and show the actual data in forms/reports, so simply: Purchase Year, Company Abbreviation and Asset Type, optionally with an added sequence-number or (assuming there's a max of one order a day) the purchase date.

When you still want a sequence-number to generate, lookup the Dcount() function and use that for counting the number of times the combination exists.

Nic;o)

[FishVal]

....
When you still want a sequence-number to generate, lookup the Dcount() function and use that for counting the number of times the combination exists.
....

Hi, Nico.

That should be DMax() function anyway. ))

Regards,
Fish

[nico5038]

Hi, Nico.

That should be DMax() function anyway. ))

Regards,
Fish

Hi Fish,

Both will work having a "closed set" of sequence numbers.
When gaps are possible, then indeed the Dmax is better in getting a new unique number, but the Dcount() will be the way to go when initializing the sequence for an established set of records, as Dmax will set all to the same value :-)

Nic;o)

[missinglinq]

The problem is that gaps are all to often going to be there, and DCount() will result in duplicate values in that case. Use DMax(), as Fish suggested, and that's never a problem!

Linq ;0)>

[zimes]

For your needs you need to look into the Format() function to get just the "YY" format, but I would like to warn you for generating these type of codes.
Just imagine what happens when adding a combination of "YY| company 3 letter abrivation | Asset Type" and it's already occurring 999 times....
Or what to do when a company's abbreviation changes...

Best way is always to use a "hidden" auto-number and show the actual data in forms/reports, so simply: Purchase Year, Company Abbreviation and Asset Type, optionally with an added sequence-number or (assuming there's a max of one order a day) the purchase date.

When you still want a sequence-number to generate, lookup the Dcount() function and use that for counting the number of times the combination exists.

Nic;o)

From what I understand is not to put in a column in the Assets table for the custom number and just have it populate the number in the forms / reports. Is that correct? If that be the case could I just then use the PK for the number then format it how needed?

As far as the triple digit at the end, I plan on resetting the number back to 001 per year. 09abcdtp001, 10abcdtp001, etc. .

Thanks for the help guys. I will look the commands up.

[nico5038]

From what I understand is not to put in a column in the Assets table for the custom number and just have it populate the number in the forms / reports. Is that correct? If that be the case could I just then use the PK for the number then format it how needed?

As far as the triple digit at the end, I plan on resetting the number back to 001 per year. 09abcdtp001, 10abcdtp001, etc. .

Thanks for the help guys. I will look the commands up.

Hi zimes,

Best would be not to use a custom (sequence) number, as a change of a companies abbreviation will causes trouble in the assigned codes.
In general for a normalized table we use a unique (or set of) field(s) to identify the occurrence needed. In your case e.g. the Companyname (or when "fixed" the Abbreviation) and a Date (sometimes when multiple rows per date can exist the time is added). In that case the unique ID is easy to understand for the user(s) and no trouble with renumbering or "gaps" in the code will exist.

For grouping (report) data per year (Your YY part) you can use the Year() function on the datefield.
When you still want to use a Year and custom sequence number you could check:
http://www.geocities.com/nico5038/xC...Insert2000.zip
Personally I used that code for generating a year bound orderID.

Nic;o)

[zimes]

Thanks Nico. I am getting some great ideas for codding this.

[zimes]

Update:
after taking a look at my tables I had to delete the AssetNumber Table and ended up moving all columns that were needed for the Auto number into my Assets Table. I started messing with VB but could not wrap my head around the Syntax of the language (which Im not very good at ANY syntax by the way). I have dug up an old SQL codding book and was able to find the Format funciton but now the statement returns an error of: "The query cannot be used as a row source." here is the code:
SELECT Assets.*, format(DateRecieved, 'yy') as AssetDate, AssetDate & CompanyID & AssetCategoryID & AssetID AS AssetNumberID
INTO Assets
FROM Assets;

the only tables I am messing with now are Company, AssetCategory, and Assets.

I have also given up on the 3 digit format because it would require a complex piece of code that I would not be able to come up with.

What am I missing in my statement?

[nico5038]

Update:
after taking a look at my tables I had to delete the AssetNumber Table and ended up moving all columns that were needed for the Auto number into my Assets Table. I started messing with VB but could not wrap my head around the Syntax of the language (which Im not very good at ANY syntax by the way). I have dug up an old SQL codding book and was able to find the Format funciton but now the statement returns an error of: "The query cannot be used as a row source." here is the code:
SELECT Assets.*, format(DateRecieved, 'yy') as AssetDate, AssetDate & CompanyID & AssetCategoryID & AssetID AS AssetNumberID
INTO Assets
FROM Assets;

the only tables I am messing with now are Company, AssetCategory, and Assets.

I have also given up on the 3 digit format because it would require a complex piece of code that I would not be able to come up with.

What am I missing in my statement?

Sorry, have been very busy at work.
For creating a new table I normally create first a SELECT query to get the needed result. Next step is to change it into an Append query (see the Query menu for the options) targeting the final table (Access will ask for it when switching the querytype). Just one thing to keep in mind when you have an autonumber ID, you need to keep that out of the fields that are "moved" to the target table.

Nic;o)