Table design involving time data (quarterly and yearly)

[AlexPYTH]

Hi everyone,

I have been working/struggling with the following project for some time and I have just decided to ask for help. Please let me explain the background as best as I can.

I want to capture Quarterly Sales and Purchases data (amounts and number of trades) according to a list of Brokers (not necessarily fixed). As an end product, I wish to be able to perform calculations on one or more Brokers' Sales or Purchases and create quarterly and yearly reports on their performance. My first question relates to time recording:

1. How do I best record a Broker's Sales and Purchase data for a particular quarter? Do I use the last date of the quarter as the reference point or do I build a synthetic field (e.g. 1Q10, 2Q10 etc)?

Currently, I have created a separate table and have done exactly the above. I am wondering whether I am over-complicating things and should just include a separate date column in my Contributions table (which includes both Sales and Purchases numbers).

Any help would be much appreciated on this.

Sincerely grateful.

AlexPYTH

 

[plog]

I would use an actual date field and then use the first date of that quarter. The reason to use a date is that you can use all of Access's built in Date functions to easily do math on that date then (Year, DateDiff, etc.). With a custom field (e.g. 02-2017) it would be up to you to reinvent those functions.

The reason to use the first date of the quarter is that its a simpler reference point to calculate--most likely it will always be the first of a month. The last date of a quarter is harder to calculate because each month has a different number of days (e.g June=30, July=31) so determining the day portion is made easier when you use the first date (its always 1)

 

[The_Doc_Man]

There are various format functions that you can use to extract parts of a date, including one that gives you the quarter and another that gives you the year.

See this link:

https://support.office.com/en-us/ar...n-Access-47fbbdc1-52fa-416a-b8d5-ba24d881b698

I, like plog, suggest keeping the actual date. But you could then easily build a query for that data where you added two fields to the query that contain FORMAT functions to give you the year ("yyyy" format template) and the quarter number ("q" format template) as two fields. If you have the query do an ORDER BY, you can certainly sort based on dates first, but you could also sort by brokers first, dates second, and that would work too.

Just remember that most of the time, using a query as input to something else works almost as well as using a table as input. Both offer recordsets. Just remember that you can't edit fields computed via the query. But if you are just doing some sort of analysis, you probably wouldn't be trying to edit anything in your source data.

 

[AlexPYTH]

Thanks to both for the info. I have done just that.

I now have 2 tables, one with the list of Brokers (tlbBrokers) and one with the Sales and Purchases numbers (tlbContributions). I have them linked with a BrokerID which acts as a foreign key in the tlbContributions. The problem I run into now is the following:

1. How do I make sure that I only have one submission of data per quarter per Broker? The idea is to have quarterly submissions per Broker, but only one submission per quarter per Broker in the same table. I have found myself mistakenly entering data for a Broker and for the same quarter twice.

Would the solution require building another table, or can the problem be solved by some indexing configuration?

Many thanks

 

[The_Doc_Man]

Using the query I mentioned, you can compute the quarter-number of your submission and see if you can do a DCount of records IN THE QUERY with a matching broker ID, year, and quarter number. If the answer is not zero, you know that you have an extra post from that broker. You might have to do some string substitution. I'm going to be sloppy for a second and post code that lacks appropriate Dim statements and supporting context.

CurQ = Format( [NewEntryDate], "q" )                'note these are strings!
CurY = Format( [NewEntryDate], "yyyy" )
CurBrkr = CStr( [NewBrokerID] )

DupCount = DCount( "*", "qryContributions", _
          "[BrokerID]=" & CurBrkr & _
          " AND [EntryYear] = " & CurY & _
          " AND [EntryQtr] = " & CurQ )

In this, you would make that query to match your table plus the two extra fields I mentioned earlier. Then if the count is not zero, you have that extraneous contribution and can decide in the context of your form what to do about it.

 

[plog]

1. How do I make sure that I only have one submission of data per quarter per Broker?

At the table level you can create a composite key (http://www.javatpoint.com/sql-composite-key). You select multiple fields and the table ensures that each new record is a unique permuatation of those fields. That way, you would receive an error message for a violation of the key if you tried to enter the same date for the same Broker.

 

[AlexPYTH]

Thank you both for the great suggestions.

Doc Man, I will take your recommendation on board as a vetting tool to ensure I have no duplicates on a separate query. Thanks for pointing out Dcount.

Plog, I have implemented your suggestion and it looks like it's working. I now have 2 fields in my main table (tlbContributions) which are primary keys. One is BrokerID (the foreign key) and the other is a Date field which I have called DateOfContribution. If I try to enter a duplicate submission, I get yelled at...which is good.

I have now created a continuous Contributions form that feeds from my Contributions table. I have created a Combo Box which filters the form according to Broker using some code, and I can now see a list of all of the submissions by Broker. So far so good.

I think my next step should be about choosing one or more Brokers from my continuous Broker List form, and having totals generated as a Report. I want a button that does the following: "Give me Quarterly Sales numbers for 2015, for Brokers 1, 2 and 3".

I am trying to visualize this, and I think I could use check boxes on my Broker List form for selecting the Brokers I want data for, and then the dates/quarters I am interested in. Or would a query be better by asking which Brokers to include and which quarters to sum up? Any ideas?

 

[The_Doc_Man]

OK, for the part "for Brokers 1, 2, and 3" - that could come from a listbox because a combobox doesn't allow MultiSelect whereas a Listbox does. You would then use the .Selected property of listboxes to decide if a particular entry was, in fact, selected for inclusion into your report.

If you use checkboxes on a continuous form, you have to be careful.

You can search the forum for "listbox selections" to see how others have approached this simple operation.

The year selection is simple and COULD be done from a combo box unless you wanted more than one year.

 

[plog]

Any ideas?

At some point you need to teach users how to create a query. I don't know if you are there yet, but its approaching.

If choosing specific brokers and quarters to total is the extent of the flexibility you want to give them then moving forward with Doc's solution is the answer. In my experience it never is though. Next they will want to sort by a third field and then they'll want to show a fourth field's values, then sometimes they will want to group it at an additional level. And before you know it, you've poorly and with much difficulty, recreated the ability to run queries in Access.

 

[The_Doc_Man]

plog, you said a mouthful there. We used to call this "requirements creep" but it was never creeping. It was full speed ahead and damn the torpedoes, icebergs, and kelp.

When I started with the US Navy Reserve as a contractor over 28 years ago, we had a small BE database hosted by an STI UNIX-like system with a dedicated O/S optimized for database hosting. We got everything up and folks liked it so well, they beat it to death. We upgraded from an IDM 500 (that was STI's "Intelligent Database Machine") to an IDM 750 and then, a few years and few turns of the corporate crank later, to a ShareBase 8000. Each machine was faster than its predecessor by at least 40%, but the moment we expanded abilities, the users expanded requirements. We beat the ShareBase so bad that we burned up a disk, thus demoting a mirror set to a single disk, then 15 minutes later burned up the remaining single disk leaving NO disk servicing a particular set of tables. We were down for 3 weeks after that debacle. I had to redesign some support hardware and get the DB engineers to work with me on a design that used ORACLE and OpenVMS clustering, but we got our abilities up by a multiplicative factor of 30. That's not 30%. Thats more like 3000% improvement. We also eliminated single points of failure at all levels and learned how to detect and disperse disk "hot-spots" - all because if you give the users a little bit more, they want a LOT more than you gave them.

So yes, you definitely need to plan ways to anticipate user growth in demands. 'cause like it or not, if you can do something, folks assume you can do more and then get crazy if you tell them you are maxed out on the CPU or something like that.

 

[AlexPYTH]

Thank you guys for the words of wisdom. I still feel like a barefoot toddler trying to navigate a floor full of mousetraps. This is scary stuff for a novice user and I have found fear to be the greatest inhibitor. The solution is perseverance.

I have taken the Query approach on this and things are kind of starting to make sense. I have managed to create a query that calculates the sum of Purchases in Datasheet view. The difficulty that I now face is that my message prompts "Which Broker?" and "Which Quarter?" will only allow me to enter one value only (e.g. Broker 1 for 1Q10). How do I make this message prompt more versatile by allowing me to include more Brokers and more Quarters? Can this be done with some combination of wildcards? Using the BETWEEN and AND keywords would not help as I would specifically need data for e.g. Broker 1 and 2 for 1Q10. Any help is appreciated.

 

[plog]

What you want to do is not novice level stuff. I'd say its even beyond intermediate.

The method you are trying is not going to work. What you want to do won't just be done by writing a query and having users enter data into prompt boxes. Because you want multiple values for specific fields (brokers, quarters) this is going to require a specific form for entering/selecting criteria, then VBA to package it all up and use as actual criteria.

 

[AlexPYTH]

Thanks plog. I will try that.