I have a database that lists stock and each stock type has a unique InternalID like the following:
The codes were set for the stock before the database was built so they cant be changed.
I have a count query that is based on the results of a query that searches for a specific stock type and weather or not its been sold, the problem I’m having is that when I query the db for all stock with the internal ID of D I get the DM stock in the query also. I’ve been using:
SELECT [tbl_stock/equipment].EquipID, [tbl_stock/equipment].InternalID, [tbl_stock/equipment].Sold
WHERE ((([tbl_stock/equipment].InternalID) Like "D***") AND (([tbl_stock/equipment].Sold)=0));
How can I just get the D code and not the DM code in the query results?
Last edited by Icerat; 08-02-04 at 08:42.
Reason: Solved original problem
Alright, you say the codes were in place before the database was built, so you can't change the primary keys...
I think you can...
What if you were to create a new table called Categories or what have you, then put all of your different categories in this table with a numeric key. Now you have a list of numeric values and a prefix for each code. Then create a anew field on your main table and use your new key to define a relationship to categories. Then remove the letters from the beginning of your part numbers. This sounds like a lot of work, but I swear to god it's worth it.
Alternately you could use an "AND" operator...
WHERE (([tbl_stock/equipment].InternalID Like "D*" AND NOT [tbl_stock/equipment].InternalID like "DM*") AND (([tbl_stock/equipment].Sold)=0));
I will strongly suggest one last time that you restructure your schema though. It will make EVERYTHING so much easier for you.
What happens when you need to generate mass sales and marketing forcasts? How about cross referencing different categories? How do you avoid bad data from alphanumeric keys? How would you add a description for each category?
Any suggestions you can give would be greatly appreciated, as I new to access I’ve learnt a lot and I’m getting there, slowly. Any suggestions on how it can be improved, layout/design/etc/etc would be appreciated like I said,
cost? (you may notice I listed price in two places, this is to account for current retail, and retail at the time of sale. I don't know if you even need to track cost, but these are the two places I would do it)
The basic breakdown is, you track your incoming inventory using the "Invoice" tables. You track outgoing or sold items using the "Order" tables. You keep tabs on your part categories and types using the "Part" tables.
This affords you all sorts of wacky flexibility if you ever need it, as well as making reporting a snap.
Want to know your current inventory on a given part? Take the total number of parts you received in the invoiceDetail table and subtract the total sold from the OrderDetail table.
Could you provide me with a sample db with the relationships defined?
No, unfortunately I cannot provide access files to anyone outside my company. I went through that "conflict of interest" fiasco once, I'm not doing it again.
Would my forms i created work with this?
I don't know what your forms look like, but I can assure you this layout can do anything your current layout does. Naturally you'd have to do a little redesigning. It comes down to whether you want to do a little work now and give yourself a solid platofrm to work from, or make a smattering of quick-fix hacks to try and make what you currently have work, resulting in major headaches in the future (some of which you are already experiencing).
Would i get the same sort of functionality with this setup?
ok i'll give it a try myself, would you be able to explain the relationships (where to join etc etc) as this is something im still getting to grips with, once i got that sorted i should (i hope) be able to build the rest upon it.