Tables

[happyaslarry]

Hello

I have written a database which I use to do quotations for my plumbing business. The materials I use are either Stock items which we keep on the shelf (copper pipe etc), Non-stock items which we hold prices and information on but are too expensive to hold in stock (boilers etc) and Special items which are things like shower enclosures which we will specify for a job and possibly never use the same one again.

I currently have two materials tables - one for Stock and Non-stock items (differentiated between with a Stocked Y/N field) and one for Special materials.

It has made writing complicated reports more difficult by having two tables and in the next build of the db I am considering merging the data. I know this is good practise but is there any reason not to merge the tables - performance etc... or anything else I should be aware of?

 

[David Eagar]

By having a Stock (Y/N) flag, all items can be kept in the one table and the rest of the project becomes a lot simpler

 

[thisisntwally]

If i read your post correctly, stock and non-stock items are already in the same table, and the question is whether or not you could/should merge the SPECIAL table with the aforementioned. The only real problem besides best practices, as you mentioned, would be that you will end up adding new Items to this table fairly regularly, items which would then just hang around collecting dust. Without knowing your table structure its kinda hard to make any real suggestions, so i'll say that you know your db best and if you think they should be merged, merge them.

(That being said, with proper normalization, you should only have to write your report once [though it may be more complicated] and adding new items will be a more streamlined process.)

 

[happyaslarry]

Thanks for the replies

I suppose what I should have been asking was whether having many records that are used only once alongside the records that are referred to many times in the same table will have any performance consequences for the user and if so, how they can be overcome.

I currently have about 600 stock items and 200 non-stock items in a table, each record having about 25 fields. The special materials table has about 1500 records, each having 12 fields.

If I merge the tables, what number of records will start to cause significant performance reductions?

I know this question is vague beyond belief but I'm just after some general guidance on the capabilities of Access.

Cheers,

Laurence

 

[SRAB]

I would not expect performance issues, those are not large numbers for Access. I run into performance problems writting reports on tables with 2 million records.