doma23
Last Activity: Jan 21 '13, 10:09 AM
Joined: May 25 '10
Location: München
-
Ok, thank you! I will think about what to implement. -
Combining multiple tools into one Master tool
Hi everyone,
For the simplicity, let's assume we have a multinational company with different businesses:
1) selling food
2) manufacturing toys
3) growing oranges
Idea was to build the one Access tool per business, all together three tools, which would be accessed independently.
After finishing the Food.mdb tool, I realized that I would actually need an access to all three tools from one place... -
Not in Access 2002 is not. ;)Leave a comment:
-
I have comboboxes on the form and I want to give the users of my tool the possiblity to scroll down and up through the values of the comboxes only with the cursor keys, without the use of mouse.
The feature is very useful.
Anyway, I've solved the problem, by adjusting the code:
...Code:Select Case KeyCode Case vbKeyDown KeyCode = 0 If Me.cmbCategory.ListIndex <> Me.cmbCategory.ListCountLeave a comment:
-
Combination of "Key Down" & "Not in List" events produce error 7777
I have two comboboxes, cmbClient and cmbCountry.
Both combos have On Key Down events set, so the user can easily go trough the combo values.
Combo Client has "After Update" and "On Key Down" event set, and it works fine.
I used After Update event to take care manually (via code) the items that are not in the list.
On combo Country, however, there was no need for that and I've used "On... -
The thing is NeoPa, that I've read quite some about normalization, and the whole time I was under impression that I'm doing things by the book.
I designed the tables and made the relationships, and I gave it quite some thought in an effort to do it properly and normalize it.
And now in the last couple of days I'm trying to build the form and the queries to manipulate these data in a way that the users will expect to do it, and I find...Leave a comment:
-
Well, not necessarily. Like I've already mentioned, I can just use different filter criteria in VBA 32 times (to match the number of products), and that works, tested.
Here is the sample code (observe bolded code):
...Code:If DCount("*", "[tblRevenues]", _ "(([ClientID]=" & Form_frmMain.cmbClient & ") AND (" & "[CountryID]=" & Form_frmMain.cmbCountry & ")Leave a comment:
-
Populating unbound form from VBA
I was populating forms quite simple before I normalized my tables. I've had this:
Client_____Coun try___Period___ Prod1___Prod2__ _Prod32
Microsoft__Germ any___ 2011 ___ 45 ___ 60 ___ 85
Client, Country and Period would give unique record, so I would use something like this code:
...Code:rs.Open "tblData", cn, adOpenStatic, adLockOptimistic sCriteria = "(([ClientID]='" & Form_frmMain.cmbClient
-
How would I set up then the formula to show the percentage changes in the crosstab query (I suppose that's what you mean by pivot)?
There would only be one field in which I must write formula, and that's Period (which would be shown as column), what do I write to compare different periods?
Query view:
Result:
Edit: SOLVED
I solved it by making another query and bringing Crosstab query...Leave a comment:
-
Then why would I need two instances in query when I can just delete the connection between Categories and Clients?
My whole point was that I thought it's possible to make Relationships in that way that it wouldn't interfere with queries. According to what you are saying that's not possible.
When I bring in the table in the query, it automatically connects the fields based on Relationship window.
Please note...Leave a comment:
-
Where do I bring in the Category table twice, in relationship window or in query?
If I bring it twice in relationship window, that doesn't change anything, because when I bring it after that in the query, it only brings one instance and it automatically makes double connection, one to Revenues and other to Clients, it doesn't take into account that there are two instances of Categories table used in Relationship window.
Therefore,...Leave a comment:
-
@jimatqsi, I don't know why you don't see the images. They should be there. I checked from another computer.
I've tried various join types, that doesn't seem to be the issue.
Categories are not missing when joined "stand alone", I've checked that.
But like Rabbit noted, it's because some combinations of ClientID and CategoryID in tblClients don't exist in tblRevenues.
@rabbit
CategoryID and ProductLineID...Leave a comment:
-
Select query doesn't show all records after join
I have 114.195 records in tblRevenues, however when I try to do the query (in order to replace various foreign keys to actual names) after I add the tblCategories, my count of records goes to 113.850, and more, after I add tblProductLines it goes down to 102.488 records.
These both tables are linked to another tblRevenues and to one other table, and that is where the problem is. When I delete connection between tblCategories and tblClients,... -
Query calculations when some records don't exist
Would it be possible later to do query calculations between two records even when one of the two records doesn't exist?
Example:
COMPANY____PERI OD___SALES
Company1___1H20 12___125
Company1___1H20 11___100
*Company1___1H2 010____? (*this record doesn't exist at all)
Company2___1H20 12___240
Company2___1H20 11___220
Company2___1H20 10___200
So I... -
All right, I'm not talking about null fields, but about not having the record at all. But, I'm gonna open new thread.Leave a comment:
-
Yeah, I've read quite some today, now things are bit clearer.
Anyway, I have another question.
I have a lot of records where revenue is zero, I would say 80% of them. Later I will need to do a lot of period comparisons. For example, 1H12 vs 1H11, and so on.
I'm wondering would it be good idea to delete all the records where revenue is zero, in order to reduce the number of the records and speed up the database?...Leave a comment:
-
Got it. Thanks!
Website is bad though. :)Leave a comment:
-
Anyone?
Damn, I thought this was simple.
Now I'm finding another problem, in the report I would need the data to look like on the second version of possible table structure.
Like this:
Comp____Country ____Period____P roduct1____Prod uct2_ ___Risk
ABC_____Germany ____ 1H ______ 125________ 80 ____ 40
ABC_____France_ ____ 1H ______ 9 ________ 7 ____ 10
How hard is it to transform the data from...Leave a comment:
-
Proper table design
At the moment I have something like this:
tblRevenues
Comp____Country ____Period____P roduct____Reven ues
ABC_____Germany ____ 1H ______ Pr_1 ____ 125
ABC_____Germany ____ 1H ______ Pr_2 ____ 80
ABC_____France_ ____ 1H ______ Pr_1 ____ 9
ABC_____France_ ____ 1H ______ Pr_2 ____ 7
tblRisk
Comp____Country ____Period____R isk
ABC_____Germany ____ 1H ______ 40
ABC_____France_ ____... -
Ok, I agree with both of you, just wanted to check what could further downsides be.
Regarding the countries and the Germany, it's not in every record.
I have the same set of companies operating in various countries, so there already is France, Italy and so on. Therefore I don't believe I need the third table.Leave a comment:
No activity results to display
Leave a comment: