Producing A Schedule

[dylcon]

Hello, I have been working on an inventory database where I will be tracking all of the transactions (incoming, outgoing, and internal adjustments) for a specific part. Curently, I have Incoming, Outgoing, and Transaction tables that tell when the transaction occured for a specific part. See relationship:




What I am trying to do next is gather all of that information into a report. My plan of attack is having a form that will allow the user to select the PartNumber, Date to go back to, etc. From there I would like a simple report (or table) that lists all of the transactions for the part. If anyone has any suggestions for how to go about this, I am all ears!

[dylcon]

Does anyone have any ideas? I need to finish this database as soon as possible and this is almost my last step..

[rpeare]

Why are you splitting up your adjustments, outgoing and incoming into separate tables, it's virtually identical data all you'd need is an indicator in a table (I, A, O) to indicate the type of transaction it was.

That aside your relationships should tell you.

use your tblPartPO as a base, link in your incoming, adjustments and outgoing put between [enter start date] and [enter end date] in the criteria of all three subtable date fields
Make the query an aggregate query (look for the sigma button, looks like a capital E on your ribbon bar) make the TOTALS line say WHERE for each of the date fields
Add your quantity fields from your adjustments, outgoing, incoming and change the totals to SUM
In the PARTID field from your tblPartPO table select the part with [enter the part number] or, if you're setting your search criteria through a form [form]![formname]![fieldname]

[dylcon]

It was more intuitive for me to split it up into three different tables. There are forms that are bound to the tables, which were easier to build that way as well. What is the best way to show what type of transaction it is (with an I, O, differnt type of adjustment key) with keeping my tables in the current format?

[rpeare]

if you follow my suggestion in the previous post you can label the columns whatever you want

For instance when you sum incomingquantity you can give it an alias of Incoming_Total

Incoming_Total: sum(incomingquantity)

[dylcon]

Well, I modified my database so that all transactions (Incoming, Outgoing, Adjustments) are in one table.

I still am not bale to achieve what I would like, however. What you described above is filtering it by PartPOID, I wanted to do it by just PartID. I also do not need it to be an aggregate query because I just want to view the records as is, without modifying them for sums or anything of that sort. I basically would just like to filter it so I can find:

1) Transaction Date
2) Transaction Type
3) Transaction Quantity
4) Transaction Notes

for a specific part type from StartDate until EndDate.

[dylcon]

Nevermind, I have figured it out.

[orange]

Do you want to let readers know what your solution is?

[dylcon]

Sure thing, orange. I attached the correct tables that show the part number (tblPartInfo) to the query and set the Criteria = [Form]![Form]![DesiredField] and then added the fields that I wanted to show and set the start and end dates from my form as the criteria for the date field on my query. I have this query running on a button on my form, which produces the table that I desired, and then I have a report built on top of the query that will allow for a more convenent way to view/print the records.

I do still have one question:

In my report, I have all of the information that I want, but in a title, textbox, or whatever is easiest, I would like to display the CustomerName and PartNumber that the data is being shown for. It was selected from the form that runs the query. I just don't know where the row source property is in reports

[orange]

I believe Reports have a RecordSource property. You could add the Name and Product to the recordsource (query/table) and they should be available in Report design -- I think.

[dylcon]

How exactly would I do this? Also, if I want to open the report that is based on the query that is runnng from my form, I should just be able to do this:

Private Sub Applycb_Click()    
    Me.Refresh
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "PartRecordQuery", acViewNormal, acEdit
    DoCmd.OpenReport "PartRecordQueryReport", acViewNormal, acEdit
   


    Me.PONumbercbo.Enabled = False
    Me.CustomerNamecbo = Null
    Me.PartNumbercbo = Null
    Me.PONumbercbo = Null
    
End Sub

but it is not opening the report

[dylcon]

Actually, it may just be printing it... the printer is downstairs and I just realized there are several sheets in there.