Stock Report by Month

[Guest]

Hi
I started to write my database 5 years ago and it's working very well but
now I'm having a problem when I asked for Monthly Stock Report. I need to
see on the report the balance of the previous months ( the previous month
stock) and the current month stock by day summed to get the real daily stock.
I had this problem before but in that time I was looking for a Yearly
Report. I'm a self learning Access and to solve this problem I created one
Query for the current year with all the records and one Query for each
previous year with the sum of the records and put together in a Union Query.
I know that it's not the right way to do it . Every year I need to add one
more Query in a Union Query and now it's impossible because I need the Report
by month required, not only the current month.
Any idea in how to do it in a MDB Database?

 

[strive4peace]

ShowFields
---


Hi Vieira,

Have you overhauled your data structures since you began the database?
AS you develop and see better ways to structure the information, it is
worth the effort to do it -- not saying it needs it ... just wondering.

in order to help you, we need to know your data structure. Here is
something you can do to document that for us:

create a new general module

paste in this code:

'~~~~~~~~~~~~~~~~~~
'NEEDS REFERENCE to Microsoft DAO library
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
'TO DOCUMENT -->
'--- click your mouse into the appropriate Sub below
'--- press F5 to run

Sub RunShowFieldsForTable()
'edit this line for the tablename you wish to document
ShowFields "Your tablename"
End Sub

Sub RunShowFieldsForAllTables()
Dim i As Integer _
, mTablename As String
For i = 0 To CurrentDb.TableDefs.Count - 1
mTablename = CurrentDb.TableDefs(i).Name
If Left(mTablename, 4) <> "Msys" Then
Debug.Print 'blank line
ShowFields mTablename
End If
Next i
End Sub
'~~~~~~~~~~~~~~~~~~
Sub ShowFields(pstrTable As String)
'by DuaneHookom
'modified by Crystal

Dim fld As DAO.Field
Dim tbl As DAO.TableDef
Dim db As DAO.Database

Set db = CurrentDb
Set tbl = db.TableDefs(pstrTable)

Debug.Print tbl.Name
Debug.Print "=========================="

For Each fld In tbl.Fields
'modified by Crystal
Debug.Print fld.OrdinalPosition & " " & fld.Name _
& ", " & fld.Type & " (" & GetDataType(fld.Type) & ")" _
& ", " & fld.Size
Next

'release object variables
set fld = nothing
set tbl = nothing
set db = nothing

End Sub
'~~~~~~~~~~~~~~~~~~
Function GetDataType(pDatType) As String
'by Crystal
Select Case pDatType
Case 1: GetDataType = "Boolean"
Case 2: GetDataType = "Byte"
Case 3: GetDataType = "Integer"
Case 4: GetDataType = "Long"
Case 5: GetDataType = "Currency"
Case 6: GetDataType = "Single"
Case 7: GetDataType = "Double"
Case 8: GetDataType = "Date"
Case 10: GetDataType = "Text"
Case 12: GetDataType = "Memo"
Case Else: GetDataType = Format(Nz(pDatType), "0")
End Select
End Function
'~~~~~~~~~~~~~~~~~~~~~~~~~

then
Debug, compile

click in the RunShowFieldsForAllTables sub
press the F5 key to run

then press CTRL-G to show the debug window

copy the results and paste into a Reply to this thread

'~~~~~~~~~~~~~~~~~~

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

 

[Jamie Collins]

Function GetDataType(pDatType) As String
'by Crystal
Select Case pDatType
Case 1: GetDataType = "Boolean"
Case 2: GetDataType = "Byte"
Case 3: GetDataType = "Integer"
Case 4: GetDataType = "Long"
Case 5: GetDataType = "Currency"
Case 6: GetDataType = "Single"
Case 7: GetDataType = "Double"
Case 8: GetDataType = "Date"
Case 10: GetDataType = "Text"
Case 12: GetDataType = "Memo"
Case Else: GetDataType = Format(Nz(pDatType), "0")
End Select
End Function
'~~~~~~~~~~~~~~~~~~~~~~~~~

Suggestion:

Public Function GetDataType(ByVal pDatType As DAO.DataTypeEnum) As
String

Also, your list of data types is incomplete e.g.

Case 20: GetDataType = "Decimal"

is an obvious omission.

Jamie.

--

 

[Guest]

Thanks for help me but I didn't use any Module before and the result that I
got I think is wrong. I have 88 Tables and the result only show me the last
19. I copy the result below but probably it will not help you.
I'll explain my Database structure in the next post, it will be a little long
==========================
0 SWBagID, 4 (Long), 4
1 Season, 4 (Long), 4
2 Warehouse, 10 (Text), 50
3 Date, 8 (Date), 8
4 Product, 10 (Text), 50
5 SwepKg, 7 (Double), 8
6 Checked By, 10 (Text), 50
7 Remarks, 10 (Text), 80

Switchboard Items
==========================
0 SwitchboardID, 4 (Long), 4
1 ItemNumber, 3 (Integer), 2
2 ItemText, 10 (Text), 255
3 Command, 3 (Integer), 2
4 Argument, 10 (Text), 255

Terms
==========================
0 Terms, 10 (Text), 50

Transportation
==========================
0 TRANSID, 10 (Text), 20

Used For
==========================
0 COUSID, 4 (Long), 4
1 UsedFor, 10 (Text), 20

Vessel Table
==========================
0 VesselID, 4 (Long), 4
1 Date, 8 (Date), 8
2 Vessel, 10 (Text), 50
3 Product A, 10 (Text), 50
4 Tons A, 7 (Double), 8
5 Product B, 10 (Text), 50
6 Tons B, 7 (Double), 8
7 Product C, 10 (Text), 50
8 Tons C, 7 (Double), 8
9 Remarks, 10 (Text), 50

Warehouse
==========================
0 WARID, 10 (Text), 50
1 Responsible, 10 (Text), 20
2 Name, 10 (Text), 30

Weight Control
==========================
0 WCID, 4 (Long), 4
1 Season, 4 (Long), 4
2 Date, 8 (Date), 8
3 Time, 8 (Date), 8
4 Machine 1A, 7 (Double), 8
5 Machine 1B, 7 (Double), 8
6 Machine 2A, 7 (Double), 8
7 Machine 2B, 7 (Double), 8
8 Min, 6 (Single), 4
9 Max, 6 (Single), 4

Weight Control 1000Kg
==========================
0 WCBB1, 4 (Long), 4
1 Season, 4 (Long), 4
2 Date, 8 (Date), 8
3 Time, 8 (Date), 8
4 Machine BB1A, 7 (Double), 8
5 Machine BB1B, 7 (Double), 8
6 Machine BB1C, 7 (Double), 8
7 Machine BB1D, 7 (Double), 8
8 Min, 7 (Double), 8
9 Max, 7 (Double), 8

Weight Control 400Kg
==========================
0 WCBB4, 4 (Long), 4
1 Season, 4 (Long), 4
2 Date, 8 (Date), 8
3 Time, 8 (Date), 8
4 Machine BB4A, 7 (Double), 8
5 Machine BB4B, 7 (Double), 8
6 Machine BB4C, 7 (Double), 8
7 Machine BB4D, 7 (Double), 8
8 Min, 6 (Single), 4
9 Max, 6 (Single), 4

Weight Control 500Kg
==========================
0 WCBB5, 4 (Long), 4
1 Season, 4 (Long), 4
2 Date, 8 (Date), 8
3 Time, 8 (Date), 8
4 Machine BB5A, 7 (Double), 8
5 Machine BB5B, 7 (Double), 8
6 Machine BB5C, 7 (Double), 8
7 Machine BB5D, 7 (Double), 8
8 Min, 6 (Single), 4
9 Max, 6 (Single), 4

Weight Control Nippon
==========================
0 WCNI, 4 (Long), 4
1 Season, 4 (Long), 4
2 Date, 8 (Date), 8
3 Time, 8 (Date), 8
4 Machine 3A, 7 (Double), 8
5 Machine 3B, 7 (Double), 8
6 Min, 6 (Single), 4
7 Max, 6 (Single), 4

Worked Hours
==========================
0 WHID, 4 (Long), 4
1 Name, 10 (Text), 30
2 Name2, 10 (Text), 30
3 JN, 10 (Text), 4
4 Shift, 10 (Text), 10
5 StartedDate, 8 (Date), 8
6 FinishedDate, 8 (Date), 8
7 ClockIn, 8 (Date), 8
8 ClockOut, 8 (Date), 8
9 Started, 8 (Date), 8
10 Finished, 8 (Date), 8
11 DailyHours, 6 (Single), 4
12 PublicHoliday, 1 (Boolean), 1
13 WAGE, 10 (Text), 2
14 Tax, 10 (Text), 2
15 Remarks, 10 (Text), 80

Worker Details
==========================
0 WDID, 4 (Long), 4
1 Date, 8 (Date), 8
2 Name, 10 (Text), 50
4 Wage, 6 (Single), 4
5 BirthDate, 8 (Date), 8
6 Remarks, 10 (Text), 50
7 Tax, 10 (Text), 2
8 SMSFixed, 10 (Text), 2
9 SMSPercentual, 6 (Single), 4

Workers Daily Job
==========================
0 Name, 10 (Text), 30
1 DJ, 10 (Text), 4

Workers Job Type
==========================
0 JN, 10 (Text), 4
1 Job, 10 (Text), 25

Workers Name
==========================
1 Name, 10 (Text), 50
2 Job, 10 (Text), 30
3 ClockingN, 3 (Integer), 2
4 CardNumber, 3 (Integer), 2
5 PayNumber, 3 (Integer), 2
6 Type, 10 (Text), 10

Workers Tax
==========================
0 Tax, 10 (Text), 2
1 Date, 8 (Date), 8
2 TLP, 6 (Single), 4
3 TSLP, 6 (Single), 4
4 TFL, 6 (Single), 4
5 TUIF, 6 (Single), 4
6 TWC, 6 (Single), 4
7 TPH, 6 (Single), 4
8 TSL, 6 (Single), 4
9 TFee, 6 (Single), 4


Workers Wage1
==========================
0 WG, 10 (Text), 2
1 JN, 10 (Text), 4
2 Date, 8 (Date), 8
3 Normal, 5 (Currency), 8
4 Over, 5 (Currency), 8
5 Double, 5 (Currency), 8

 

[Guest]

I'm continuing my last post.
Our company bagged powder products that came in Vessels for other companies,
so we are a subcontractors. I started this database 5 years ago only for our
own control on the bagging side, but during this 5 years our job expands for
other areas like loading the bagged product in trucks, control the bulk
products received, sweepings generated, etc. I needed to "expand" my database
on the same way. Till now I'm the only that use the database to insert
records, but it's growing and probably another people will use it too and I
need to make it more "friendly use".
The database has 55MB and some of the tables has 5000/10000 records each.
I have 2 different situations.
Case A - Report by Season (yearly)
Parameter Value to enter: SEASON (e g 2006, 2005,...)
Fields name: Product (Text)
Date (Date/Time)
Season (Number - Single)
Bagged (Single)
Received or Returned (Single)
Transfer In or Out (Single)
Loaded (Single)
Damaged (Single)
Stock on Floor (Expression)
Records: 1st record is the Total Sum of the previous Seasons
The next are showed by date
The values come from 6 different Tables that I linked them in a
Union Query

Case B - Report by Month
Parameter Value to enter: Month and Year (e g MAR07, AUG06,...)
Field name: Vessel (Text)
Date (Date/Time)
Product (Text)
Received (Number - Single)
Bay Transfer (Number - Single)
Loaded (Number - Single)
Bagged (Number - Single)
Stock (Expression)
Records: 1st record is the Total Sum of the previous months
The next are showed by date
The values come from 4 different Tables that I linked them in a
Union Query

I hope that these information are enough to someone help me. How I said
before I'm a self learning in Access and I don't know many things in VB.

Thanks for everybody