Database design and Queries - Having a Brain Lock on this.

Posted on 2001-06-24
Last Modified: 2010-05-02
I am writing a fairly simple Point of sale system and am having a brain lock on how to do the database and suggestions are needed - Here's the scenario - My company buys and sells computer related items as well as selling new items, We must track who we bought the items from and quantities in stock, Where I run into a brain lock is how to track this properly in the Quantities - Since we may have a New item and an identical used item in stock, I can't have the QTY in stock be the same (New and used needs to be seperated) - I have tried making seperate fields (NewItems and UsedItems) - but at the time of sale and at time of purchase the item from the customer, this makes it difficult (I am not that great at database stuff as you can tell) - I can't seem to find any good examples to use, I am hoping that someone has had a simular problem and can help.
Question by:flosoft
  • 3
  • 2
  • 2
  • +3

Expert Comment

ID: 6222927
Well - first, if you cannot sell the new and used as the same item then you must want to distinguish them from each other even though they are the same item number (yes?).

Now - you must be using specific identification on these items if you are tracking who the vendor is.  If you have 15 new and used in stock, how are you tracking which one came from which vendor?  Do you have serial numbers or other identifications?

Author Comment

ID: 6223057
The best idea so far is to create a used table and a new table and a qty table and then use the autonumber to create a unique number for each item, which will also be our numbers for barcode printing, however I haven't figured out how to make that work - especially when scanning in the product at time of sale - My database programming isn't that great, So I am unsure if I could pull that off - Then there's the matter of putting new products into the database and tying that to either an Offstreet (Customer) purchase or a purchase from a vendor, As the title states, this has my brain in lock status.

If anyone has suggestions or an example that is simular - It will be greatly appreciated.

Expert Comment

ID: 6223070
I dun have any specific similar example for u. anyway, u can always refer to MSDN & book online in order to help u in coding & getting the overview of database design.

from your suggestion about the db layout, it should be able to cater for the needs, unless u have more things to add. make your tables normalize, u'll be able to expand your db later.

get help from MSDN or even microsoft support knowledge site.

good luck.

Expert Comment

by:Valliappan AN
ID: 6223272
>then use the autonumber to create a unique number for each item

ya, may be you could use an autonumber for your table, having new and used items. So, that when you are selling, you also store this auto-generated info in the sales table. Now, you could identify, from which supplier you got it like this:

Select SupplierCode from Sales,Purchase Where Sales.BatchNo=Purchase.BatchNo

where, BatchNo is the auto-generated number of your purchase table.

Hope you could do this, had I understood properly.


Expert Comment

by:Valliappan AN
ID: 6223275
And for your new and used problem, you could have a field like type and store 'N' for New and 'U' for used, something like this.

Hope this helps.
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

LVL 16

Expert Comment

ID: 6223314
Maybe posting this question on Access forum would helps.
LVL 14

Accepted Solution

Matti earned 200 total points
ID: 6225685

>a fairly simple Point of sale system, such thing does not exist, there are a lot of data to store a lot of reports to run, you do pay taxes there? pay salaries etc? everything is connected together and side by side systems makes extra work and these are bad.

Indexes help to search the database more quickly but they do need more storage space and this need to be consider what is need to index
thus you use to search the record may be you need names or something else.

Also those second hand parts, maybe you need to give they an item number which always begins from 20000 or similar which is lot more than new products volume is.

Make customer table where key is the customer number and consider other indexed fields too.

Make vendor table where key is the vendor number and consider other indexed fields too.

Make salesman table where key is the salesman number, this can target the sales to person if it is a commission system.  

Make items table where key is the items number and name and code you can get these codes from barcodes of the items. This is how to make it easier, if you do not have barcode scanner get one that not a expensive, very easy to use.

You need:

Item number
Item name
Vendor number
Price(that price you pay)
Quantity of add
Tax percentage or category

Now make two tables for the "storehouse", one for sales and other for buying.
Do one entry as something happens if the event is sale then make this to sales table and purchase then to other side.

Item number
Item Name (just in case of problems)
Customer number
Salesman number
Quantity of sold
Tax (calculated using tax percentage)
Price(that price customer pays on this row)

Item number
Item Name (just in case of problems)
Customer number
Salesman number
Quantity of sold

This two table system is good if something gets corrupted it is easier to build in allerts for this, and it makes report queries more simpler to track the number of total items etc.

In case of serial numbered parts like processors hard-disks you need to make each as separate product.
if this sales is high-frequency, need to make a separate database or table of these serials and in case of warranty matter you can found it there.

All those tables which are connected to other tables needs indexes to make the relations work.
You do make queries for the reports and data forms of the program interface.

Now there are no invoices capacity, but basically the data is, but that's another thing.
Normally secondhand stores use own serial for each item and they do label this in the product.



Expert Comment

ID: 6225723

I may be way off base here, but if you are going to use specific identification as you mentioned (scanning each one in and seperately tracking each one), then consider the following:

Create an item table with the following format(with additional fields as necessary):

ItemNumber - self explained
ItemDescription - self explained
ItemLocation - ex: diff Warehouse or even bin location

Then create a Serialized table:

ItemNumber - relationship w/ above
ItemSerialNumber - your serial number assigned
ItemCondition - N(ew), U(sed)
ItemVendorNumber - Vendor Number of Vendor (assuming you have a Vendor Table)
ItemCost - specific to that purchase
ItemPrice - what you will charge customers
Quantity - self explained

The Serialized table is a one-to-many relationship to the ItemMaster table.

When you want to know about quantities, you can get recordset like:

"SELECT ItemMaster.ItemNumber, " & _
"First(ItemMaster.ItemDescription) AS ItemDescription, " & _
"ItemMaster.ItemLocation, SerialItems.ItemCondition, " & _
"Sum(SerialItems.Quantity) As QuantityOnHand " & _
"FROM ItemMaster INNER JOIN SerialItems ON " & _
"ItemMaster.ItemNumber = SerialItems.ItemNumber " & _
"GROUP BY ItemMaster.ItemNumber," & _

This will give you total quantity of items by condition (new, used).

This gives you the flexibility to know at any one time how many you have in total, or grouped by new/used.  
Also - you can do a select statement that would give you only those that are from a specific vendor as well, and distinguish between new and used.

This is a very basic example.  If this is on the right track, let me know or ask questions or something and we can get what you need.


Expert Comment

ID: 6225736
I really hate that double crap (is that too strong of language??)

anyway - huge apologies.
LVL 14

Expert Comment

ID: 6225756

Ok, that do sometimes happen here, more often in those fresh questions, in the usual manner we do type in and submit. I did not reload it either as the Q was a day old, just used halfhour for typing, it was a matter of ten minutes.


Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now