How To Get Dynamic Sum
Aug 25, 2006
Hey,
I made a table Products: order number (key), name, supplier, price,...
I copied it into a query, and made a form of it, so I can look up products by order number with a combo-box. Now I'm into my next step (and it gives me a headache):
In the form I added a new text box, where I should put the incoming amount of a product in, and another text box to put in the products that are empty.
Now the question: how can I make a query that counts the amounts of incoming and empty products, as well as the difference between those two, in a way I can do multiple additions?
I hope you know what I mean by dynamic sum: start stock=0 first delivery: +3==> stock 3 +next delivery: +6 ==> stock 9,...
If anyone has some idea...
Thanx in advance
Kev
View Replies
ADVERTISEMENT
Jun 30, 2006
I have a form that shows a list of all of my records in my database. I want to be able to click a button called "Report" and have that print a report that has all the records I have filtered on my form. I have a report in the format that i want it in, however, currently it prints every record and not just what is shown on my form. (The form is dynamic and I want the Report to be dynamically based on the form) HELP PLEASE!
View 6 Replies
View Related
Oct 8, 2006
Hi guys, I am trying to create a form or subform that should be created after a button is pressed and some complex filetering is occured and records are saved in a multiple arrays so I need to generate a form and controls according to those array values. Please help.
Actually I can not get data directly out of the query its just too complex bla bla bla. Finally I have three arrays each array has same number of values and I want to generate a form on the basis of the values of the array. Please Help me. I have tried creating form but Could someone please show me the right path.
many thanks
View 1 Replies
View Related
Oct 12, 2006
Now, I don't know if this topic should be in forms, queries or reports, so I decided to post it here in general until someone gives me a big bollocking and send this topic elswhere :D
I have a table with loads (50+) of columns which hold information about people in our company.
What I want is a form? that allows me to tick the information I want and then displays the result in a report.
So, today I want a list with Name, address, town and phonenumber, but tomorrow I might need a list with Name, phonenumber, passport number and expire date.
In short, I need to make a 'dynamic'? report that displays the options I ticked out of the 50+ options (does this make sense?).
I searched for it but no luck so far.
Ta in advance
View 4 Replies
View Related
Jan 24, 2008
Hi,
How can I determine the length (size) of a dynamic array so I can iterate over it?
Thanks,
Mike
View 2 Replies
View Related
Jul 17, 2005
Hello,
I have two tables: Categories and Subcategories.
Categories contains one field: CategoryName
Subcategories contains two fields: ParentCategoryName and SubcategoryName.
In a third talbe I want to select a category from a listbox, then have the subcategory listbox update automatically with the possiblities. Is this possible?
Thanks.
View 1 Replies
View Related
Jul 14, 2007
Not sure that this fits under table but I believe that is the root of the solution, so we'll try it here.
I'm building a database to simplify the process of creating a PC build sheet for my company. I would like it to track pricing and everything as well so here is where the problem comes in:
Say right now a floppy drive costs our customer 10 dollars. In a few months, we could still be using the exact same floppy drive, but the price has dropped to 9 dollars. If I run a report to display all computers sold in the past year (including pricing), I want the floppy to have the price that the customer paid at the time; not the latest 9 dollar price only.
Correct me if I'm wrong, but here is my thinking:
I could run a macro to rename the $10 floppy drives and update it in all old records (though I have a feeling I'd have to pull a trick out to mess with UIDs) then update the price on it for the list of options when building out a computer, but it seems like I should be able to automate this better.
Possibly some sort of linkage table?
I'm just trying to keep this as clean as possible so I'm not digging back through this a year down the road.
View 3 Replies
View Related
Mar 3, 2006
i have a database that imports files with dynamic amounts of fields, runs querys on the data and outputs it to a speadsheet. The The problem is instead of making it run querys on each column, i have it running on them all collectively. Now i want to change it so a query is run on each column then output to its own spreadsheet. Does anyone know how to do this?
View 1 Replies
View Related
Feb 7, 2007
I have a form where a user chooses different criteria from listboxes and then I run out and format the SQL statement.
This is the query that I am trying to replicate using VBA code. Note: the Location_ID, Ship_Day, and Final_Dest come from the values in the listboxes.
I am getting a syntax error and I cannot find it anywhere. Also, is there an easier way to do this?
SELECT [(Table) Denton Routing].LOCATION_ID
, [(Table) Location].NAME
, [(Table) Location].CITY
, [(Table) Location].STATE
, [(Table) Location].REGION
, [(Table) Denton Routing].UNIQUE_LANE_ID
, [(Table) Denton Routing].CARRIER_ID
, [(Table) Denton Routing].[SHIP DAY]
, [(Table) Denton Routing].[DELIVERY DAY]
, [(Table) Denton Routing].[TIME AT LOCATION]
, [(Table) Denton Routing].STOP_NUM
, [(Table) Denton Routing].NO_OFF_STOPS
FROM [(Table) Location] INNER JOIN [(Table) Denton Routing] ON [(Table) Location].[LOCATION ID] = [(Table) Denton Routing].LOCATION_ID
WHERE ((([(Table) Denton Routing].UNIQUE_LANE_ID) In (SELECT UNIQUE_LANE_ID
FROM [(Table) Denton Routing]
Where [(Table) Denton Routing].Location_ID = "13176AA"))
AND (([(Table) Denton Routing].[SHIP DAY])="MONDAY"
AND [(Table) Denton Routing].Final_Dest = "DENTON"));
Here's the code:
Private Sub cmdRunQuery_Click()
Dim Db As DAO.Database
Dim QD As QueryDef
Dim where As Variant
Set Db = CurrentDb()
' Delete the existing dynamic query; trap the error if the query does
' not exist.
'On Error Resume Next
'Db.QueryDefs.Delete ("Dynamic_Query")
'On Error GoTo 0
where = Null
where = "WHERE ((([(Table) Denton Routing].UNIQUE_LANE_ID) In (SELECT UNIQUE_LANE_ID FROM [(Table) Denton Routing] where"
where = where & " [(Table) Denton Routing].[Location_ID]= '" + Me![Text35] + "'"
where = where & " AND [(Table) Denton Routing].[Final_Dest]= '" + Me![List29] + "'"
where = where & " AND [(Table) Denton Routing].[Ship Day]= '" + Me![Combo46] + "'))"
MsgBox (where)
Set QD = Db.CreateQueryDef("Dynamic_Query", _
"Select SELECT [(Table) Denton Routing].LOCATION_ID, [(Table) Location].NAME, [(Table) Location].CITY, [(Table) Location].STATE, " & _
" [(Table) Location].REGION, [(Table) Denton Routing].UNIQUE_LANE_ID, [(Table) Denton Routing].CARRIER_ID, [(Table) Denton Routing].[SHIP DAY], " & _
" [(Table) Denton Routing].[DELIVERY DAY], [(Table) Denton Routing].[TIME AT LOCATION], [(Table) Denton Routing].STOP_NUM, " & _
" [(Table) Denton Routing].NO_OFF_STOPS FROM [(Table) Location] INNER JOIN [(Table) Denton Routing] ON [(Table) Location].[LOCATION ID] = " & _
" [(Table) Denton Routing].LOCATION_ID " & (" where " + Mid(where, 6) & ";"))
DoCmd.OpenQuery "Dynamic_Query"
End Sub
View 5 Replies
View Related
Oct 19, 2007
Say I have 3 fields in my table: FstName, MidName, LstName.
I want to be able to dynamically change the sort order of a query to different sort orders using the same fields via a form.
I may want to see the records sorted by LstName, MidName, Fstname; and another time see the list sorted by Fstname, Lstname, MidName; and other times by any combinations possible using the 3 fields.
I see 3 options: filter by form; programatically change the SQL Order By clause; or concatenating the table fields together in the desired order via a custom function and sorting by the function result.
Is there any difference in query/form performance between the 3 options?
View 3 Replies
View Related
Feb 25, 2005
NEW PROBLEM! :)
Sorry for the double post.
View 2 Replies
View Related
Apr 14, 2005
Hi all, I'm currently strugging with an Access problem and wondered if anyone here could give me some ideas. This is fairly complicated to explain, so please bear with me!
Basically, I have a query that pulls out a list of users, and another query that pulls out of a list of possible applications that that may be assigned to them.
What I need to do is present this in a form, so that my app user can browse through the list of users, and assign/deassign applications to that user. I'm thinking of presenting this along the lines of a list of users down the left of the screen, a list of apps along the top of the screen, and a checkbox for each app/user combination.
The complication is the list of possible applications is different every time the form is loaded. There are 1500 possible apps, and ANY combination of these may be available.
My most recent attempt at this was to find the list of apps, and then (using vba) create a temporary table, with a "User id" field, and then a "yes/no" field for each app that is available to this group of users. I can then present this in a subform, allowing the tickboxes to be viewed and modified for each user/app combination.
However, my problem is that when I dynamically try to alter the recordSource of the subform, the field names are different each time, so it doesn't display the app names I need. I've searched Google etc, and the only relevant suggestion is to have multiple subforms, and display the one that suits. However, with such a huge number of possible combinations of apps, I cannot take this approach.
So, is there any way that I can easily update the subform to have the correct columns? Or am I going about this in entirely the wrong way?
Any suggestions will be gratefully received - I'm hitting a brick wall with this at the moment and don't have much hair left to pull out!
View 1 Replies
View Related
Apr 3, 2006
I have a main form that displays different part numbers and the corresponding material type for each item. For example,
Part 1 metal
part 2 candy
part 3 label
I have created specific subforms based on the type of the material. I want to place a command button on each record, and when the button is clicked, the correct subform based on the material code will open. For example, the above 3 items are displayed on the form, if the user clicks the button next to candy, then the candy subform opens and if the user clicks the button next to label, then the label subform opens.
Thanks for helping solve my question.
Jeff
View 1 Replies
View Related
Aug 22, 2004
hi,
i'm having a problem making my report dynamic, i have a query which returns the
payments due in the next month, my question is, if i have a form based on this query,
how can i make it so that, if i need to print an invoice for one of the customers, lets say the currently displayed record,
how can i limit the customers displayed in that report (invoice) to just the current record that i want the invoice for?
Thanks
View 4 Replies
View Related
Mar 21, 2012
I have several tables but lets focus on two Table1 and Table2
Table1.ID is a foriegn key in Table2.NewID
I want the default value for Table2.NewID to be the MAX of Table1.ID; that is I want Table2.NewID to auto default/select whatever is the highest ID of Table1.ID. I have toyed with this as much as I can by entering different SQL statements in the the default field of the datasheet. And by defining different Macros for OnFocus event.
View 1 Replies
View Related
Aug 2, 2005
I am looking to basically update the hyperlink base every time the database is opened to be the folder that the database is currently in.
We are trying to make the database and supporting information portable, so that people can copy the entire folder anywhere on their computer and all of the links will still work. I can't put all of the info in the database because we are already pushing the size limit.
I would like to keep it from getting terribly complicated, as others will have to update this database with more info in the future and make the links themselves.
If there is a better or different way of getting the same result without going through the hyperlink base, that is fine.
Any help would be appreciated.
Thanks.
View 14 Replies
View Related
Aug 24, 2005
Hello to all,
I created a dynamic report based on cross tab query, the header is a sub report.
Number of lines and rows depend on the data typed in a form.
Here is my problem. When numbers of rows exceeds for exemple 15 the header and detail line continue on the line below i would like the report continue on another page (see below).
Actualy i have this:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
16 17 18 19 20
line1xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxx
line2xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxx
etc..
I would like this:
page 1:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
line1xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
line2xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
etc...
Page 2:
16 17 18 19 20
line1xxxxxxxxx
line2xxxxxxxxx
etc...
THANKS IN ADVANCE FOR HELP.
VINCENT
View 2 Replies
View Related
Dec 8, 2005
I have the following Tables in my database:
Categories | Sub Categories | Products
Categories has the following fields:
Floors | Exterior | Interior
Subcategories has the following fields:
Carpet | Vinyl | Wood | LapSiding | Shingles | Insulation
I have a form called Order Details and when entering the products through my form I have two "Lookup" fields, the first which is CatID which will return a dynamic listbox in the "Categories" field containing "Floors, Exterior, Interior" pulling from the Categories table.
The second is SubCatID which will return a dynamic listbox in the "Sub Categories" field containing "Carpet, Vinyl, Wood, LapSiding, Shingles, Insulation" pulling from the Sub Categories table.
I am however having problems with my third which is "ProdID". I am wanting it to also be created dynamically through a "Lookup" on the Products table. I want when I choose for instance, "Floors" and "Carpet" from the Order Details that only the choices that match those two ID's populate the "ProdID" field and only the products for carpet not for Shingles or other sub categories.
I hope this makes sense...
TIA for any help that you can offer.
Donm
View 14 Replies
View Related
Dec 16, 2005
As much as I would like to tell my boss he's insane I'd also like to keep my job. I know this is possible, but it sounds very complicated. Basically, I need a way to generate queries on the fly. This is geared towards someone who doesn't know SQL of course. I know basically what he's looking for, comparing sales over some period of time. However, he might want annual numbers, quarterly numbers, percentages, overages/shortages, and any kind of sales related query you can think of. The only way I figure I can do that is to have a form build the SQL statement, save the SQL statement as a query, then have him open the query. Is there another way I can do this without necessarily saving a query first?
View 6 Replies
View Related
May 8, 2006
I've got a database that I need to import a vacation days table from an excel sheet. The biggest deal with it all is that the excel file & range it needs to search for the data is dynamic, actually I also need to be able to scan different sheets within the excel file.
Now, im not new to all this stuff, ive done all sorts of complicated excel manipulation in VBA as well as queries and custom processing, but I've been away from it all for roughly 8 mo. so im rusty.
If anyone has a suggestion about how i could go about loading the excel file (dynamically) and pulling information from it (i will be doing aggrigates like SUM & Count, however i can program these manually if need be.)
Thanks
-ReAn
View 2 Replies
View Related
Jun 14, 2006
Dear all,
I want to generate a different drop down box dependent upon the criteria of another selection on a form.
For example, when the user selects "fish" from the drop down, the query criteria lists only those animals whose animal type is fish. If the user selects "reptile" from the first drop down, the query criteria changes, so the next drop down box lists those animals whose animal type is reptile.
To summarise:
Choices in drop down 1:
Fish
Reptile
Choices in drop down 2:
If Drop down 1 = Fish
Cod
Haddock
Shark
If Drop down 1 = reptile
Snake
Lizard
Is this possible, and how do I go about structuring it.
Thanks,
Steve
View 1 Replies
View Related
Jun 14, 2006
Dear all,
I want to generate a different drop down box dependent upon the criteria of another selection on a form.
For example, when the user selects "fish" from the drop down, the query criteria lists only those animals whose animal type is fish. If the user selects "reptile" from the first drop down, the query criteria changes, so the next drop down box lists those animals whose animal type is reptile.
To summarise:
Choices in drop down 1:
Fish
Reptile
Choices in drop down 2:
If Drop down 1 = Fish
Cod
Haddock
Shark
If Drop down 1 = reptile
Snake
Lizard
Is this possible, and how do I go about structuring it.
Thanks,
Steve
View 1 Replies
View Related
Jun 14, 2006
Dear all,
I want to generate a different drop down box dependent upon the criteria of another selection on a form.
For example, when the user selects "fish" from the drop down, the query criteria lists only those animals whose animal type is fish. If the user selects "reptile" from the first drop down, the query criteria changes, so the next drop down box lists those animals whose animal type is reptile.
To summarise:
Choices in drop down 1:
Fish
Reptile
Choices in drop down 2:
If Drop down 1 = Fish
Cod
Haddock
Shark
If Drop down 1 = reptile
Snake
Lizard
Is this possible, and how do I go about structuring it.
Thanks,
Steve
View 1 Replies
View Related
Jun 14, 2006
Hi,
I have the following query:
SELECT *
FROM qryForCreateReportSold
WHERE (((qryForCreateReportSold.Fund) Like '*STP*') AND ((qryForCreateReportSold.[Sale Date])>=#7/1/2005# And (qryForCreateReportSold.[Sale Date])<=#6/14/2006#));
The user fills out a form and hits run query and the value STP gets passed into the query criteria.
My situation is that there are claaifications such as S/STP, E/STP etc and the problem is all these projects also show up whereas I want projects ONLY
under the STP classification.
I guess the problem is due to the fact of the *STP* which means pick all records that have an STP in them...
If I manually get into the query and remove the * * then the projects ONLY with STP show up.( which is exactly what I need)
Can some one suggest something to remedy the situation.
Thanks
View 4 Replies
View Related
Dec 13, 2006
Hello everybody! i want to create a table consisting of 2 columns. one column may contain constant values and the other has to contain dynamic values (values of the second column must depend on the values of the first column). For example if the constant of the first column is ID then the variable of the second column will be ID+5. Something like that below
Column 1 Column 2
ID1 ID1+5
ID2 ID2+5
ID3 ID3+5
. .
. .
. .
Could anyone give me a tip about that? Thank you in advance...
johann
View 2 Replies
View Related
Apr 18, 2007
Supposed I have the following tables:
TableMonthlyReport consists of the following fields:
*IDStore
*Year
*Month
*IDCatalog
*IDItem
Quantity
TableGoods:
*IDCatalog
*IDItem
ItemName
IDSupplier
The asterisks indicates the index keys. While TableMonthlyReport has 2 links of relationship with the TableGoods on IDCatalog and IDItem.
I have a data entry front end for monthly report. I have no problem to have the record source for this purpose.
I just need that the sort order of the item (in the front end) in a way that they could be managed differently on different period. For example, in March to April 2007 I want the items shown in the following order: A B C D ... While in May to August 2007 I want the items shown as B D A C ....
I tried to create an order table just like this:
TableGoodsOrder:
*IDCatalog
*IDItem
*ValidStart
ValidEnd
OrderItem
It is linked with the TableGoods on IDCatalog and IDItem.
The first order list (March - April 2007) will have the ValidStart of 703 and ValidEnd of 704. While the next order list will have the ValidStart of 705 and 708. The last 2 digits stand for the month, while the first digit is to indicate year.
The SQL query for the record source works but I can not enter any value. So, the idea of having a special table for sort order is not OK.
Can anybody give me a clue for the solution?
View 2 Replies
View Related
