Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Eager_Student
Starting Member
1 Post |
 Posted - 2004-05-27 : 17:05:35
|
| My apologies in advance if this request is not in keeping with proper forum etiquette. I’m still a little new at this.I need to build an inventory database. This database is an adjunct for an accounting program (QuickBooks), which cannot satisfactorily process inventory the way I need it processed, to wit; item by location. I relay this so that anyone wondering why there is no need for Customer or Vendor information will know why.Note: I did not see a method of uploading this small database (792KB) so I will gladly email it to whoever responds along with anything that will better explain where I am going with this. Here is the gist of what the database has to do.· Items are purchased and stored “in one of nine (9) locations”.· Items are sold “from one of nine locations”.· I must produce a report, Inventory on Hand, any given day of the week of item quantities “by location”.(I have been able to meet this demand only by reprinting an Excel report on which I manually track additions and deletions to inventory. This is proving unwieldy.)I have added a join (junction?) table at the suggestion of a helpful forum member. The database has two (2) forms thus far (one each for purchases and sales) but, ideally, I would like to add a sub form to both that will show me “item by location” for the “item keyed” into the parent form.I have been told that this could best be accomplished by creating a Union Query. When I get to this point I am still in the weeds as I rely on Access Help and in its step 4 it tells me to “point to SQL Specific, and click Union”. At this point all I see is an SQL Button that does not give me that option.I think I have set up the basic design correctly but will gladly listen to those far more experienced in this matter. I have taken a couple of Access courses at a local tech school near home but all instruction seems to be built around Contact Management examples. This has not seemed to help me thus far as I’ve only needed Access thus far for two databases; the one I just described and once for payroll reports.If you can offer assistance, please keep it as close to laymen’s terms as possible (especially SQL String instructions) as I have no programming background. I would gladly exchange accounting system design advice, which I do have for help on this project. |
|
|
timmy
Master Smack Fu Yak Hacker
1242 Posts |
Posted - 2004-05-30 : 19:50:56
|
| The first thing you need to be able to do is visualise the structure of the data. Once this is done, the rest can usually fall into place.From what you've indicated, you need:- A products table (containing an item for each product you handle)- A locations table (containing a list of the 9 locations you use)- An inventory table (containing an item per product per location - eg. If all 9 locations stock product A, there would be 9 entries for product A in the inventory table)- A sales table (which I would assume to be one row item per product per sale - ie if a customer bought 2 x Product A and 3 x Product B, then there would be 2 entries in the sales table for that sale)The sub-forms you mentioned should not need Union queries - unless the data is more complicated that you're letting on. Tim |
 |
|
|
|
|
|
|
|
Basic Design Help