Results 1 to 4 of 4
  1. #1
    rlonger is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Jan 2013
    Posts
    2

    Beginners Trouble, Please Help

    Hello All,

    I am very new to access and am having a problem that I hope someone can help me with.

    Context : I am building a database to keep track of items called C#s. Each item has only one C# to define it so that a different C# would refer to a different item.

    I have three tables:


    1) (CNumberT)
    A table of general information for each C# that very rarely changes. Each record in this table has information for just one specific C#. I am using the C# as the Primary Key.
    2) (CNumberRecievedT)
    A table that displays all shipments of recieved C#s. The table displays the C# recieved, the quantity of C#s recieved in the shipment, the date the shipment was recieved, and the Shipment ID which I set as the primary Key using AutoNumber. An employee will enter the information in a form once a shipment is recieved.
    3) (CNumberUsedT)
    A table that displays all used C#s. The table displays the C# used, the quantity of C#s used, the date that amount was used, and the UsedC# ID which I set as the primary key using AutoNumber. An employee enters the information in a form at the time that quantity of C#s is taken from the stock room.

    So, my question is: How do I create a query that sums the total amount of C#s recieved for all shipments of one specific C# using the table CNumberRecievedT? As well as the sum of all the total C#s used for a specific C# using the table CNumberUsedT? Remembering that there will be multiple shipments for the same C#, and the same C# item can be used multiple times so that there are multiple records for that one C#.

    My goal is to use these to values to know the Current On Hand quantity of each C#.

    Thank you very much for any help!
    -Ryan

  2. #2
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    Welcome to the forum. Since the CNumberReceivedT and CNumberUsedT tables are similar in structure why not just use one table with a field to distinguish received versus used or even more simply a negative quantity to indicate used items?


    tblTransaction
    -pkTransID primary key, autonumber
    -fkCNumberID (foreign key to your cnumber table)
    -QTY
    -fkTransTypeID foreign key to tblTransType
    -dteTrans (transaction date)
    -txtReferenceNo (your shipmentID for received items or some other reference ID for used items)

    tblTransTypes (2 records: received, used)
    -pkTransTypeID primary key, autonumber
    -txtTransType

    In terms of summing, you would use an aggregate query and group by CnumberID and filter it for one type (i.e. received). Create a second query for the other type (used). Then a third query to subtract the used from the received by joining the two earlier queries.

    If you decide to just use a negative value for the quantity used then you would not have the type field and your would use 1 aggregate query.

    You might also want to look at Allen Browne's site regarding calculating quantity on hand.

  3. #3
    rlonger is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Jan 2013
    Posts
    2
    Thank you very much jzwp11.

    Your right, having one table for both the usage and shipments is much more logical. And also, making usage negative and shipments positive simplifies the whole process.

    I appriciate the guidence!

  4. #4
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    You're welcome.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Beginners questions
    By LoftusAK in forum Access
    Replies: 4
    Last Post: 04-04-2012, 06:34 AM
  2. Replies: 5
    Last Post: 04-01-2012, 12:50 PM
  3. Iff trouble
    By JackT in forum Access
    Replies: 3
    Last Post: 08-18-2010, 02:28 PM
  4. Beginners question
    By nashr1928 in forum Access
    Replies: 4
    Last Post: 07-19-2010, 11:20 AM
  5. a beginners question
    By bluelondon in forum Access
    Replies: 0
    Last Post: 07-12-2007, 04:54 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums