473,396 Members | 1,678 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,396 software developers and data experts.

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

zimes
4
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
Apr 11 '08 #1
9 2233
nico5038
3,080 Expert 2GB
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)
Apr 12 '08 #2
FishVal
2,653 Expert 2GB
....
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
Apr 13 '08 #3
nico5038
3,080 Expert 2GB
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)
Apr 13 '08 #4
missinglinq
3,532 Expert 2GB
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)>
Apr 13 '08 #5
zimes
4
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.
Apr 14 '08 #6
nico5038
3,080 Expert 2GB
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)
Apr 14 '08 #7
zimes
4
Thanks Nico. I am getting some great ideas for codding this.
Apr 17 '08 #8
zimes
4
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?
Apr 21 '08 #9
nico5038
3,080 Expert 2GB
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)
Apr 29 '08 #10

Sign in to post your reply or Sign up for a free account.

Similar topics

0
by: Redd | last post by:
The following is a technical report on a data modeling project that was recently assigned to me by my professor. I post it so that anyone else who is studying databases and data modeling can have...
2
by: Greg Strong | last post by:
Hello All, I've written code in a test database with test data. Everything seems to be working except compact database in VB code per http://www.mvps.org/access/general/gen0041.htm. The reason I...
0
by: Viorel | last post by:
Working as a beginner with data objects in Visual Studio 2003 and C#, I use the "Generate Dataset" command in order to generate automatically the dataset objects based on data adapters. Generated...
9
by: Jenden0 | last post by:
I'm new to C# (and Microsoft in general) so this may be a simple problem, but I haven't been able to figure it out yet. I've got a database with a number of different tables and I want the user...
34
by: priyanka | last post by:
Hi, I was wondering if we could parse or do something in the executable( whose source language was C). How can I use some scripting language like perl/python to find out the information about...
5
by: BMeyer | last post by:
I have been losing my mind trying to parse an XML document (with nested child elements, not all of which appear in each parent node) into a DataGrid object. What I want to do is "flatten" the XML...
9
by: sellcraig | last post by:
Microsoft access 2 tables table "data main" contains a field called "code" table "ddw1" is created from a make table query of "data main" Goal- the data in "code" field in needs to...
10
by: Sjaakie | last post by:
Hi, I'm, what it turns out to be, fooling around with 3-tier design. At several websites people get really enthusiastic about using custom dataobjects instead of datasets/-tables. While trying to...
14
by: jmDesktop | last post by:
I have a food menu. Each area, like beverages, grill, etc. have items under them, Coke, Tea, Coffee would be under beverages for example. I want to add a new drink to beverages. In my database...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
0
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
0
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.