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

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.
Who is Participating?

Improve company productivity with a Business Account.Sign Up

MattiConnect With a Mentor Commented:

>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.


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?
flosoftAuthor Commented:
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.
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

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.
Valliappan ANSenior Tech ConsultantCommented:
>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.

Valliappan ANSenior Tech ConsultantCommented:
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.
Richie_SimonettiIT OperationsCommented:
Maybe posting this question on Access forum would helps.

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.

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

anyway - huge apologies.

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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.