Many Tables One View???

[SoundPad]

Hi.

In short i am designing a database to keep stock at a guitar shop. Each guitar, keyboard etc has it's own unique stock number created with it's category code (e.g Electric Guitar = GE) and a 4 digit number indicating it's position in the grander scheme of things.

Basically i've created a table for each Category (to allow me to serialize by category) and i want to access all the data through one Layout. Now is there a way to access all 30 tables through one layout and if so how do i do so in the shortest possible amount of time.

I attempted linking all the fields in the different tables together (as each category has identical fields within it) to no avail and allot of effort.

Help would be greatly appreciated.

Cheers

elliot

[comment]

Why have separate tables to begin with?

[Raybaudi]

...i've created a table for each Category (to allow me to serialize by category) ...

Hi comment

it's always the same problem ! He wished a custom serialization !

[SoundPad]

You got me. Safes allot of time if i can create a serial automatically, validates itself etc. Thought the logical way to go was to give each category a different table thus allowing me to use the auto enter serial, ultimately i can have GA5432 and GB5432 with no problem. Happy Days!

[comment]

Yeah, well... it wasn't really a question.

[comment]

You can still have GA5432 and GB5432 with no problem. The only difference is that the numbers in each category will not be consecutive. I don't think that should be a concern.

EDIT: The highlighted portion is WRONG - see below.

Edited January 17, 2007 by Guest

[SoundPad]

Ok, so what your saying is i can have all my products in one table and still have the serials. I've already tried this but to no avail. Admittedly this would be a far easier method, i'm just doubtful. It's essential the serial is auto entered as if it's entered manually you need to find the last serial in that category and make sure you don't duplicate an existing one. So is there another way???

[SoundPad]

Ok so looks as if i should be looking at Serializing by Category again. Is there any reason why assigning a Category a table won't work (as far as slow everything down)?

[comment]

I am not entirely following you. Why is serializing by category required?

[comment]

I have made a mistake earlier - I didn't notice the numeric portion of the two serials in your example was identical. What I meant that the numbering scheme could be easily organized like this:

GT001

GT002

KB003

DK004

GT005

...

So there are gaps in each category, when viewed on its own. But I don't see why anyone should care about those.

[SoundPad]

Yeah i see your point but unfortunately for me the client insists otherwise! I think the point he's getting at is you have the ability to estimate the age of a product (due to the numerical position of the serial) and more importantly it's what they've always done. I guess people don't like change...

So anyway in your opinion should i carry on down the road of assigning each category a table and using some sort of global search to find specific products or should i invest some serious time into cracking this serialize by category?

[comment]

Some points for your client:

1. There's no need to estimate the age of a product; with a computerized database, you can have the EXACT age of the product diplayed whenever and wherever you want;

2. With a separate table for each category, you will need to call in a developer every time you need to add a new category;

3. Count the steps required to move a mis-categorized product to another table, compared to simply selecting another category;

4. If you don't like change, stay with the shoebox.

Now, it IS possible to serialize by category within a single table. The problem is it's not 100% reliable. There's a very slight - but very real - chance of generating a duplicate serial. This has been discussed many times, here are just a few example links:

http://fmforums.com/forum/showtopic.php?tid/171870/

http://fmforums.com/forum/showtopic.php?tid/183472/

http://fmforums.com/forum/showtopic.php?tid/181611/

http://fmforums.com/forum/showtopic.php?tid/183166/

[SoundPad]

Cheers links helped a beaut