473,326 Members | 2,023 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,326 software developers and data experts.

Database creation query

Hi folks,

Hoping somebody can help me here with what is probably a fairly easy
question. I have decided to create a database for a load of stock which
I have to manage in my office as opposed to using a great big unwieldy
spreadsheet. I had hoped that I could use forms to add and remove stock
from the store etc etc.
My problem is this, I have created a table named products which holds
details of each of the individual products we hold and have also
created another table called locations which lists the various
locations within the store where things can be put. I did now need some
way of bringing the actual stock into the database. I did not make the
products table a list of stock as some items are held in quantities of
more than 1 and are defined by their serial number and repeating these
items in the products table seemed to go against the rules of database
design such that there should be little or no repetition in tables. So
should I create another table which lists the actual stock held or does
anybody have any other fancy ideas as to how i can get around this.
Thanks to anyone who takes the time to read this and gets to the end.

Paula

Nov 13 '05 #1
4 1532
On 16 Mar 2005 05:11:26 -0800, pa***************@telindus.co.uk wrote:

Indeed you need at least a third table:
tblInventory
ProductID
LocationID
QuantityOnHand

For things with serial numbers (assuming it's important to track
that), it can get tricky. One idea is to have a table in one-to-many
relation with tblProducts, another one is to enter these as individual
products in tblProducts. A lot depends on your exact requirements, the
kinds of reports you want to run, the amount of time you want to
invert in the app, and frankly your proficiency with the tool.

One idea is to look at the many templates that are available for
Access. Depending on your version you get to them in different ways.
Check with your help file.

-Tom.

Hi folks,

Hoping somebody can help me here with what is probably a fairly easy
question. I have decided to create a database for a load of stock which
I have to manage in my office as opposed to using a great big unwieldy
spreadsheet. I had hoped that I could use forms to add and remove stock
from the store etc etc.
My problem is this, I have created a table named products which holds
details of each of the individual products we hold and have also
created another table called locations which lists the various
locations within the store where things can be put. I did now need some
way of bringing the actual stock into the database. I did not make the
products table a list of stock as some items are held in quantities of
more than 1 and are defined by their serial number and repeating these
items in the products table seemed to go against the rules of database
design such that there should be little or no repetition in tables. So
should I create another table which lists the actual stock held or does
anybody have any other fancy ideas as to how i can get around this.
Thanks to anyone who takes the time to read this and gets to the end.

Paula


Nov 13 '05 #2
have a look at this first. It could save you time and headaches....
http://www.allenbrowne.com/AppInventory.html

Nov 13 '05 #3
Hi there,

Thanks for that guys but nobodys really answered the question. Im sure
in principle there are countless examples of db apps where items are
stored in a table as products or whatever but the fact is that when you
have more than one of these items and these items are individual enough
to have serial numbers, what is the best solution for this short of
bloating the products table to take all the individual products held.
Problem with that is I had hoped that the products table could even
reflect products which are no longer held or are currently out of stock
but if the products table is to become a reflection of currently held
stock then it cant really do this. Im sure almost everything ive just
said clearly demonstrates the fact that i dont really know what im
talking about but ill hold my hands up on that score.

Help please guys !!!!

Paula

Nov 13 '05 #4
On 18 Mar 2005 14:38:56 -0800, pa***************@telindus.co.uk wrote:

It's hard to give detailed advice without doing a detailed analysis.
One thing you can consider is to keep the products table clean, and
entering the SerialNumber at the InvoiceDetail level.
ProductID=1, Descr=Harddrive 200 GB
InvoiceDetail: ProductID=1, SerialNo=ABC123

-Tom.

Hi there,

Thanks for that guys but nobodys really answered the question. Im sure
in principle there are countless examples of db apps where items are
stored in a table as products or whatever but the fact is that when you
have more than one of these items and these items are individual enough
to have serial numbers, what is the best solution for this short of
bloating the products table to take all the individual products held.
Problem with that is I had hoped that the products table could even
reflect products which are no longer held or are currently out of stock
but if the products table is to become a reflection of currently held
stock then it cant really do this. Im sure almost everything ive just
said clearly demonstrates the fact that i dont really know what im
talking about but ill hold my hands up on that score.

Help please guys !!!!

Paula


Nov 13 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Albretch | last post by:
Is there such thing as a table or database creation time in the SQL standard, that you could avail yourself of? I mean do databases keep this info. I think they do since they are like little OSs...
1
by: Mike | last post by:
How Do I use an ODBC database in Query Analyzer? Presumably I could then join a table from another database to a SQL table to either pull information together or pass information back and forth...
2
by: Alex | last post by:
Subject: Looking for an XML (database-based) Query Reporting Tool/advice First off, let me apologize if this thread is somewhat off topic... PLEASE REPLY TO: xml@solex-bi.com I am looking...
3
by: Hans Horn | last post by:
Howdy, is there a way to speed-up or tune the database creation? On my laptop (DB2-UDB PE) it takes 2 minutes to create a virgin database. This virgin, btw. is 30MBytes heavy. Is there a way to...
1
by: Jatinder | last post by:
How to give database creation privelege to a user on Windows? Regards, Jatinder
0
by: ShamnaPR | last post by:
Hi All, How to speed up the database creation in DB2.Am working in Windows platform( windows 2000). Its taking alot of time for me to create a database. can u help me for this. Thanks,...
3
by: Limunski Magarac | last post by:
Hi all :) My apologies if I posted in the wrong groups, but I just jumped in MS SQL waters, so any guidance will be appreciated. What I'm trying to do is the following process: present...
0
by: KishorDhembare | last post by:
Dim file As FileInfo = New FileInfo(Path.GetFullPath("TextFile1.txt")) Dim backupScript As String = file.OpenText.ReadToEnd() Dim strCommand As String = backupScript 'Dim...
2
by: Sergei Musson | last post by:
Hello, everyone. We have met a problem recently: CREATE DATABASE query takes up to 60-70 seconds. This happens when the total number of databases exceeds the number of 400. And the more...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

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.