Brainstorming - Open for Suggestions

[dcavaiani]

We are a Custom Carpentry Business

I have a Materials Purchased table with a Cust# and Job# KEY. The other fields are:

PurchaseDate
LineAmount
PartDesc
VendorName
vendorINV#

From our Vendors, when we purchase Materials, we can get 2 different things:

1) the individual weekly Vendor "cash register" receipts
2) a weekly summary statement of all purchases from the Vendor.

Problem:

Assuming the "cash register" receipts will NOT have the Customer/Job# on them ...

And that the Individual cash register receipts will have to be later manually marked (each line) with the appropriate customer/job#

And assuming that we WILL be able to get a weekly summary of purchases downloaded into our system using a "dummy" Customer/Job# key - which would be the Vendor's ID.

Best Way to DO THIS IS ?? :

"what FORM and/or CODE would make the MATCHING UP of the Individual "now coded" Receipts TO THE DOWNLOADED DATA - SO THAT THE Customer#/Job# we SELECT WILL REPLACE the "dummy" key we applied to the Summary download."

Basically, we are looking for suggestions regarding the FASTEST method to get this "MATCH-UP" done ..

 

[Solo712]

We are a Custom Carpentry Business

I have a Materials Purchased table with a Cust# and Job# KEY. The other fields are:

PurchaseDate
LineAmount
PartDesc
VendorName
vendorINV#

From our Vendors, when we purchase Materials, we can get 2 different things:

1) the individual weekly Vendor "cash register" receipts
2) a weekly summary statement of all purchases from the Vendor.

Problem:

Assuming the "cash register" receipts will NOT have the Customer/Job# on them ...

And that the Individual cash register receipts will have to be later manually marked (each line) with the appropriate customer/job#

And assuming that we WILL be able to get a weekly summary of purchases downloaded into our system using a "dummy" Customer/Job# key - which would be the Vendor's ID.

Best Way to DO THIS IS ?? :

"what FORM and/or CODE would make the MATCHING UP of the Individual "now coded" Receipts TO THE DOWNLOADED DATA - SO THAT THE Customer#/Job# we SELECT WILL REPLACE the "dummy" key we applied to the Summary download."

Basically, we are looking for suggestions regarding the FASTEST method to get this "MATCH-UP" done ..

Hi,
a lot depends on what you use to download data. What would be the input dataset:Excel, CSV,... ? Then, since you say that the "dummy" key - manually inserted, I assume - will identify the job and the customer (youself that is), it should be a cinch to filter it and append the relevant data to the actual db table. Can you actually automate matching the Job# (eg. by matching the date, the vendor, and the price, to the job order) to the date of the receipt? If so, then you can have a module do that.

Best,
Jiri

 

[dcavaiani]

Hi,
a lot depends on what you use to download data. What would be the input dataset:Excel, CSV,... ? Then, since you say that the "dummy" key - manually inserted, I assume - will identify the job and the customer (youself that is), it should be a cinch to filter it and append the relevant data to the actual db table. Can you actually automate matching the Job# (eg. by matching the date, the vendor, and the price, to the job order) to the date of the receipt? If so, then you can have a module do that.

Best,
Jiri

It will be no problem getting the SUMMARY into Access. As far as a AUTOMATED MATCH UP, we would have to do more DATA ENTRY of all or most of the FIELDS again from the Individual cash register receipts.

As you said, "should be a cinch to filter it and append the relevant data to the actual db table" - that is where we are at right now. Wondering what might be the best/simplest/quickest to FILTER the FORM so that while we are looking at the now individual coded receipts, the scrolling and searching of the downloaded data for that VENDOR - FINDING the MATCH WILL BE EASIEST. Might just need to do some trial and error here to see what we deem the best way ...

 

[The_Doc_Man]

The FASTEST method to get the matchup done is to never enter an unmatched receipt or receipt line item. However, since it is unlikely (from your workflow description) that you would not easily be able to change the business model, your only other option is that your receipts and line items have to sit in a table without a job number or customer number sometimes, waiting for this assignment.

I see no actual issue with receipts or line items sitting around without a job number because it seems to me that such is already the case in your business model as you just described it. If that is a true aspect of the business model, then you WANT that to be part of the database representation of that model. (That last statement was NOT a question. You are building the database as a map to the actual territory of your workflow model.)

Think about it this way: Your table that defines the JOBS provides the Job number as a primary key (PK) for THAT table. It seems likely that you could have multiple expense receipts for a single job, right? So that is already a one (job)/many (receipts) table. 1/M relationships ALLOW for the case that you have NO receipts (yet), and you theoretically could have that exact case - entering the job before you start work on it.

The receipts table either uses some natural key on the receipt itself OR uses a synthetic key as ITS PK. The job number can't be a PK for receipts since you can buy more than one round of supplies for a job, right? I wouldn't use customer number as a PK, either, since you would HOPE to get repeat business, thus meaning that customer numbers would be in a relationship of:

customer - 1/many with job and job - 1/many with receipt and reciept - 1/many with receipt line item

Therefore, build a form based on a query that looks in your receipts or line-items table for unassigned expense items (Job # is zero). Have a combo box that allows you to associate an extant job number to the current item being reviewed. When you make that assignment and update the line-item record, requery the form because the item you just assigned is no longer eligible to be assigned in this particular way.

Now, it is POSSIBLE that you could have multiple jobs represented on a single receipt. In that case, the receipt is NOT a child of the job number. It is an INDEPENDENT table - which is OK and would make sense. THEN your hierarchy is customers > jobs > line items AND receipts > line items. But that's OK, Access can do that.

 

[Solo712]

It will be no problem getting the SUMMARY into Access. As far as a AUTOMATED MATCH UP, we would have to do more DATA ENTRY of all or most of the FIELDS again from the Individual cash register receipts.

As you said, "should be a cinch to filter it and append the relevant data to the actual db table" - that is where we are at right now. Wondering what might be the best/simplest/quickest to FILTER the FORM so that while we are looking at the now individual coded receipts, the scrolling and searching of the downloaded data for that VENDOR - FINDING the MATCH WILL BE EASIEST. Might just need to do some trial and error here to see what we deem the best way ...

Not knowing your setup, I am not sure. Normally, the matching of the vendor/job (partdesc code, purchase price(?)) would be done through purchase order module.

Best,
Jiri

 

[The_Doc_Man]

There is another comment to be made...

You have a SUMMARY of purchases and you have the individual receipts. But this question is about what is in the summary. Is the summary a "recapitulation" of the capital expenses or is it a true, aggregated summary (total) of expenses? What level of detail occurs in the summary?

If it is an aggregated summary, you can't do what you asked without playing combinatorial & permutation games to see which combination of receipts potentially matches up to the summary line. On the other hand, if the details are present in the summary report you mentioned, you have redundant data sources - good for checking and accountability but a pain in the toches when something is missing. So in order to best answer your question, we need to know more details about the physical / actual relationship between the receipts and this summary file.

 

[dcavaiani]

The download from the Vendor will have ALL of the line by line Detail. Our Owner is concerned that his workers may fail at times to turn in the individual receipts and therefore some purchases and not making it onto the invoices to our customers. This process would show the downloaded receipts which remain UNMATCHED and therefore allow for some remedial actions possibly.

 

[dcavaiani]

Let me ask this more speifically:

On the FORM I load, how can I maybe have 3-4 buttons - each of which will re-filter.resort the data By:

button 1) Amount
button 2) PartDesc
button 3) Inv#
button 4) Purchasedate

 

[CJ_London]

Have you considered creating purchase orders in your db? The PO can be linked to the customers/jobs at the time of creation - perhaps include an estimate of cost at the same time - might come from your quotation system. Also at the time of creation the PO can be linked to a supplier either at the header level or for someone going shopping to several suppliers on one trip at the line level. All depends on your purchasing practices.

When receipts come in, with the exception of shop till receipts your supplier should be able to feed back the PO number on their documentation. With shop till receipts, if the person who has gone shopping has a copy of the PO, then can simply return it together with the receipt(s).

At month end review, any PO's without a receipt can be easily identified. You should know who went shopping - you presumably gave them a cash advance - so you know who to chase, and anyway whoever gave cash out of the cashbox will want a receipt and change.

Yes, more work before making your purchase, but less after and makes for better financial accountability.

 

[MajP]

f the form is in tabular view I like to make all the labels clickable to sort. But you can use buttons Similar to outlook
Here is an example. In this case the sorted label gets a line underneath it.

'------------------------------------------------------- label Sorting ------------------------------------------------
Private Sub lblBreeding_Click()
  SortForm "BreedingRound, Cage"
  RemoveLine
  lblBreeding.FontUnderline = True
End Sub

Private Sub lblCage_Click()
  SortForm "Cage, BreedingRound"
  RemoveLine
  lblCage.FontUnderline = True
End Sub

...... more labels
Private Sub lblSeason1_Click()
  SortForm "Season"
  RemoveLine
  lblSeason1.FontUnderline = True
End Sub


End Sub
Private Sub SortForm(strSort As String)
  Me.OrderBy = strSort
  Me.OrderByOn = True
End Sub

Private Sub RemoveLine()
  Dim ctrl As Access.Control
  For Each ctrl In Me.Controls
    If ctrl.Tag = "SortField" Then ctrl.FontUnderline = False
  Next ctrl
End Sub

 

[dcavaiani]

f the form is in tabular view I like to make all the labels clickable to sort. But you can use buttons Similar to outlook
Here is an example. In this case the sorted label gets a line underneath it.

'------------------------------------------------------- label Sorting ------------------------------------------------
Private Sub lblBreeding_Click()
  SortForm "BreedingRound, Cage"
  RemoveLine
  lblBreeding.FontUnderline = True
End Sub

Private Sub lblCage_Click()
  SortForm "Cage, BreedingRound"
  RemoveLine
  lblCage.FontUnderline = True
End Sub

...... more labels
Private Sub lblSeason1_Click()
  SortForm "Season"
  RemoveLine
  lblSeason1.FontUnderline = True
End Sub


End Sub
Private Sub SortForm(strSort As String)
  Me.OrderBy = strSort
  Me.OrderByOn = True
End Sub

Private Sub RemoveLine()
  Dim ctrl As Access.Control
  For Each ctrl In Me.Controls
    If ctrl.Tag = "SortField" Then ctrl.FontUnderline = False
  Next ctrl
End Sub

Gonna give this a try - Looks like exactly what I want !!

 

[The_Doc_Man]

Dynamic sorting can occur in many ways. A form can even replace its own .Recordsource and then run a .Requery if needed. I have done this more than once. The trick is that you need an event. MajP suggested a click on the label to indicate that it is the sort order that you want.

I used the method where the SELECT portion of the .Recordsource query was in essence a constant, as was the WHERE clause - but the ORDER BY clause was kept separately. By default, my records were sorted chronologically so the clause was "ORDER BY XactDate" and in the form's Open event, I built the final query by concatenating the constant SELECT and WHERE clauses with the default ORDER BY clause.

Then, if I clicked the button to change sort order, I rebuilt the .Recordsource with the same constant SELECT and WHERE clauses but tacked on a different ORDER BY clause and did a .Requery. Worked just fine. This is an ALTERNATE to what MajP suggested, not a claim of better replacement. It is ALWAYS a matter of what works for you.

 

[Cronk]

It was pointed out to me a long time ago that if the recordsource or rowsource is changed in code, it is unnecessary for a Requery.

 

[dcavaiani]

f the form is in tabular view I like to make all the labels clickable to sort. But you can use buttons Similar to outlook
Here is an example. In this case the sorted label gets a line underneath it.

'------------------------------------------------------- label Sorting ------------------------------------------------
Private Sub lblBreeding_Click()
  SortForm "BreedingRound, Cage"
  RemoveLine
  lblBreeding.FontUnderline = True
End Sub

Private Sub lblCage_Click()
  SortForm "Cage, BreedingRound"
  RemoveLine
  lblCage.FontUnderline = True
End Sub

...... more labels
Private Sub lblSeason1_Click()
  SortForm "Season"
  RemoveLine
  lblSeason1.FontUnderline = True
End Sub


End Sub
Private Sub SortForm(strSort As String)
  Me.OrderBy = strSort
  Me.OrderByOn = True
End Sub

Private Sub RemoveLine()
  Dim ctrl As Access.Control
  For Each ctrl In Me.Controls
    If ctrl.Tag = "SortField" Then ctrl.FontUnderline = False
  Next ctrl
End Sub

This is awesome and easy to apply!! Thanks. Only thing I may have wrong is that the underline of the 1st sorted column does not go away when I select a different column - both underlines stay on. Also, how could I bold or change font color in the sorted column ?

 

[MajP]

All the labels that are used for sorting need the tag field set to
"SortField"

If ctrl.Tag = "SortField" Then ctrl.FontUnderline = False

 

[Cronk]

If there is a need to toggle between ascending/descending then

if me.orderby = strSort then
   me.orderby = strSort Desc
else
   me.orderby = strSort
endif

 

[dcavaiani]

If there is a need to toggle between ascending/descending then

if me.orderby = strSort then
   me.orderby = strSort Desc
else
   me.orderby = strSort
endif

Having a bit of trouble - maybe cause I'm on 2002? The above does not compile ... the Desc line. Might it be because I am using 2-3 fields as the sort sequence - for example: SortForm "customer, purchasedate,extended"

 

[The_Doc_Man]

Cronk, probably true now that a reset of the .Recordsource forces a .Requery. Not sure it was always true so with my faulty and imperfect memory I merely added a belt to the suspenders so I wouldn't lose my pants.

 

[MajP]

Having a bit of trouble - maybe cause I'm on 2002? The above does not compile ... the Desc line

The code is incorrect. It is a string. Should be.
me.orderby = strSort & " DESC"

However, that will only work with a single field in the sort. I usually have multiple fields in the sort.
This would likely need to get modified to something more robust.

Private Sub lblCage_Click()
  SortForm "Cage, BreedingRound"
  RemoveLine
  lblCage.FontUnderline = True
End Sub

to

Private Sub lblCage_Click()
  dim strSort as string
  strSort = "Cage, BreedingRound"
  If me.orderby = strSort then
    sortForm "Cage DESC, BreedingRound" 
  else
    sortForm strSort
  end if
  RemoveLine
  lblCage.FontUnderline = True
End Sub

There is not need to requery after setting the sortby

 

[MajP]

Here is better code for the SortForm. This allows you to pass in multiple fields and go descending on the first one.

Private Sub SortForm(strSort As String)
  Dim strRep As String
  If Me.OrderBy = strSort Then
    'add descending to first field only
    strSort = Replace(strSort, Split(strSort, ",")(0), Split(strSort, ",")(0) & " DESC")
  End If
  Debug.Print strSort
  Me.OrderBy = strSort
  Me.OrderByOn = True
End Sub