eliminate possibility of two tables having same Autonumber value 2

[hwkranger]

I have tickets, and I have a separate table for each type of ticket (since each type of ticket has different fields it doesn't make sense to have 100 fields in a table for 10 different type of tickets)

anywho - I don't want any tickets to have the same name as any other tickets - yet I want to have separate tables for each type of ticket. Is this possible? Originally I thought it would be by adding a letter infront of the ticket number to show the differences in the ticket, but I can't figure out how to do this (I tried \V in the format to add a V infront of the ticket number but it formats it like this: 9 = V9, 10 = 1V0 you can see my dilemna) And then I thought that this might not be a good idea, since they offset each other, and the offset field is Numeric (this is a preexisting database where someone put 20 tickets in one table creating a table with 54 fields.. I'm trying not to add new ticket types to the old table)

Anyways, is it possible for me to keep uniformity between the tables such that using regular autonumber two tables will not generate a ticket with the same number?

Randall Vollen
National City Bank Corp.

 

[GingerR]

you can make the V in front by ="V"&TicketID

say you have a data entry form, and you are on Ticket V. and you hit a button to create a new record. in that button's OnClick event, you can put in additional code that will set the TicketID = "Blah"&whatever the autonumberID is.

me.TicketID = "V" & ID

or depending on how you have things set up--say you are on a main switchboard-type form, and you want to make a new ticket, and you choose to make a "D" ticket (or whatever) and click a button. on that button's OnClick event, you have

Open the D Ticket form
set the TicketID to "D" & ID

ok?

but i HAVE to ask: what's with the diff tables for each ticket? what kinds of fields are so not-the-same?

have fun--g

 

[hwkranger]

I know how to concatinate using VB, it's within the assignment of the autonumber that I want to be able to do this. Which I thought I was able to do by typeing \V& inside the "Format" option in the table design but it ended up not working how i wanted it to.

The reason I don't want to do this within VBA on the forms is b/c there are maybe 20 - 30 different forms that use the Ticket ID. Since this is the case I don't want to have to change every single place where a ticket can be generated forcing the letter infront of the ticket Number.

Since I'm only adding "virtual" ticket numbers (which aren't really tickets since they are virtual) I wanted a V infront of the ticket number.

There is no real reasoning behind me not wanting to put them in the same table as the rest of the other tickets aside from the fact that theres 52 fields ALREADY in the Ticket Table. There's 20 different types of tickets with fields such as customer, address, account, offset account, Po box, Branch, SSN, City, State, ZIP, TranCode, Shipped from, ShippedTo, etc.. etc.. there's 52. Each ticket uses about 10 fields. None of the tickets have exactly the same fields as other tickets but many have fields that overlap other tickets. Example 18 of the 20 types of tickets are issued by a branch.

I'm not even sure if using a LETTER infront of the Ticket Type would be best, I do know that I need to make sure that between tables that there are none of the same ticket numbers. (Currently everything is in 1 table I'm added a new ticket type that uses 3 new fields. that will bring my table to 55 fields if I add this ticket to the old ticket table)

I guess in closing, Maybe I shouldn't make such a fuss. Since if the fields are empty then they aren't returning any information or taking up space.. are they? I just don't like the idea of returning 16,000 tickets with 40 fields that are "null".

Randall Vollen
National City Bank Corp.

 

[GingerR]

i agree with your last statement. the old days are over; space is cheap. the trade off on having a poorly-designed database isn't worth it.

have you looked closely at your design? is for example CUSTOMER and their related info (SSN, address, etc) repeated thru your table? if so, then instead you should have a CUSTOMER table with that info in it. then in this Ticket table, you'd only have field CustomerID which is related to the Customer table.

anyhow, if you want more insight in to the design--if you think it may not be at its optimum right now, let us know and we'll help.

good luck--g

 

[hwkranger]

Thanks for the tips.. I know about the fundamentals of database design, mostly what i"m worried about at this point is making my database faster.. It's very large and at atimes VERY slow.

No, none of that data repeats. I work for a Bank. A ticket is what happens mostly when thre is an error. Changes of an error repeating on the same customer - is slim to none. And if it did happen - I wouldn't want to create a system that expected it.



Randall Vollen
National City Bank Corp.

 

[johnnymac43]

If the numbers and types of fields for any ticket can be predicted with reasonable accuracy, you could try this approach.

> Define a number of generic fields, eg. Date1, Date2, Currency1, Currency2, String1, String2 etc.

> Map each field (that is not common to all ticket types) for each ticket type to a generic field, with as much commonality as possible, eg. if most but not all ticket types have an account number, then use the same generic field for this item whenever it occurs, and use it for something else that will fit (or leave it empty) for any ticket type where there is no account number.

> Keep all your data in one table, in a mix of specific (eg. Client Name) and generic fields.

> Set up a reference table, with one record for each ticket type, having the generic field names from the main table (on text fields, not date etc.), with each field containing the detail (for report headings etc.) of the contents of the field of that name for that ticket type in the main table. Unused fields would be blank for a ticket type.

With this approach, most new ticket types could be fitted to the existing structure. If an additional field was required, eg. Date3, adding this field to each table would be a simple process that would have no impact on your existing data.

HTH

John

 

[MichaelRed]

See faq700-184 It can (with a bit of modification) provide the single value regardless of the table. Study the function and understand what (and how) it is working and generate the equivalent which returns only the number value and never re-sets. The instantation of the recordset and the retry-timeout are the key issues for the function.




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[GingerR]

here are some things that might help make it faster:

ACC2000+: make sure in table design that SUBDATASHEET is set to NONE.

index fields properly; any fields that you later search/filter/do calculations on.

ACC2000+: turn off autocorrect feature (in production, not in developement if desired) - TOOLS+OPTIONS+GENERAL tab uncheck Name Autocorrect stuff

 

[hwkranger]

I want to thank everyone for their Help. I would have replied sooner but I have a horrific eye infection and took some time off.

Unfortunately what I want, and what I know is right are two different things. I don't want my database to get slower than it is by increasing the size of my table, yet I don't really want to separate the tickets from each other since they ideally belong together.

Since this is an inherited project that is 97 not 2k, i'm stuck with putting the ticket in the same table as all the rest of the tickets and adding 3 or 4 more fields to my table with 52 fields already! Oh well.

Since it hasn't been moved to 2k, I'm responsible for that too. whoo-hoo.

Thank you Ginger for all your posts, although I'm not using any of them and I knew most of the information - your reiteration helped me realize that I need to keep uniformity.

Thanks to MichaelRed Too, since although his FAQ didn't quite address what I wanted in the manner I wanted (I didnt want to use VBA) I did like his code it was informational and I will probably use ideas from it in the future. (and yes, I understood it, not everyone is an idiot... lol)



Randall Vollen
National City Bank Corp.