I am new to Access and am trying to reduce a dataset I am working with to make it managable for Excel. I have three columns which have three unique parameters and one column with numberical content.
Column A Column B Column C Column D NJ Red Monday 10 NJ Red Tuesday 20 NJ Yellow Wednesday 30 NY Red Monday 35 NY Green Tuesday 40 NY Green Wednesday 60
I want to elimiate column C from the dataset, and sum column D for every unique combination of Column A & Column B. I have figured out how to reduce down the dataset to get a list of all unique combinations of Column A&B,what formula I can use in a query to sum column D on my table for every unique combination of column A&B. This is a simple SUMIF in excel, but my dataset is too large for excel.
Each new row of record needs to have their own line item number starting from 1 with criteria if order number is the same then carryover line item number on that record. I know how to get line item starting from 1 to last by assigning unbound control field with entry "=1".
Is it possible to run a SQL command to update a field within a table with random numbers?
More specifically - random long integers linking back to an ID (autonumber) field in another table?
Background to this is, I have multiple static data tables related to each other by long integer identifiers (autonumbers)
The structure is fine but I haven't been provided with the actual data yet - but for development purposes, I need to work on other functionality which requires that this data be present.
So I want to fill my table with dummy data such that I can go off and work on the remaining functionality, but then just go back and clear it all out once I get the actual data.
I have one 'main' static table, which links back to other tables, which I have already populated with dummy static (i.e. company names, locations etc) Now I want to go into my main table and populate those fields in each record with a random ID. I don't mind doing this field-by-field (there's only a handful) but I've a lot of records in there (~1000) so I'd rather not do this record-by-record.
The code I am modifying looping through Me.controls collection to identify appropriate control types. Is there a way to influence the order (sequence) of that traversing? Or in different words, is there a way to assign by design certain item number to a given control?
I'm currently working on fixing an older 97 database that I've updated to 2010. I have just populated the Drop down box with about three fields. Ideally what I want to do is after having selected the item number from the drop down box I then hit the button that creates a report with the information about that item number.
At the moment...If the box is blank it reports all the item numbers, however if i fill the box(select an item) it returns nothing i.e the report is blank.
I've looked at the query that builds the report, there are only three fields that populate the. location, part number and description. from a tbl called MainDetails
the only other thing: If([Forms]![frmReports]![FLoc] Is Null,[FUNCTIONAL LOCATION] Is Not Null,[FUNCTIONAL LOCATION] Like [Forms]![frmReports]![FLoc]) it has a criteria of <>False *FLoc is the drop down box *functional location is the location field. I believe all the above does is populate the report if Floc is empty.
What can I put to make the report generate what ever I pick in the drop down box 'FLoc' source the three fields from the 'MainDetails table'.
I have a report that i can order through buttons on report or openargs. Is it possible to get a field that would count the number of time an item appears i.e., if I sort it by employee, it would count how many times each employee is on the report, if i sort it by TypeOfMalfunction, it would count how many times each appears.
I'm creating s simple database to track my printer toner supplies, what i want is that..every time I select a date (using date picker) it will automatically deduct 1 from the total number of toners on stock. Right now, i have to input 1 in a field and it will update the remaining toners.
I hope you donít mind my using this forum as a learning tool. I am running an Access 2007 database. I am trying to create a query from 3 tables: TblTrainingPrograms containing the following fields Program Number (unique value) Program Title Program Date
TblInstructors with these fields InstructorName, address, etc.
TblInstructorProgram with the fields InstuctorID ProgramNumber(linking this instructor to a particular training) InstructorNumber(in the case of multiple instructors for the same training, the user may add new records and enter ď1Ē, ď2Ē or ď3Ē here)
I need to run a query that will display even multiple instructor programs in one record, so that Instructor# 2 will appear in another field in the same record rather than having 2 or three records displayed in the datasheet view. Iíve tried several combinations, but when there is only one instructor, the Program does not appear in the query results. I donít really know a lot about SQL, but Iím hoping there might be a way to do this that way. Any help would be appreciated.
Hi, i am wondering how to reduce the size of access. i have included pictures within forms which obviously made the database bigger (it is currently around 12mb). However i have tried to delete the pictures to reduce the size, this method did not work at all. The database remains the same size. does anyone have any suggestions on how to solve this problem i am having?
I have a form that simply lists the items: DEPARTMENT_NBR and DEPARTMENT_NAME
In the table: DEPARTMENT_TBL
When I edit the field DEPARTMENT_NBR and it is in error (must be between 01 and 99) I want to put out a message in a MSGBOX and SetFocus back on the DEPARTMENT_NBR.
I coded the MSGBOX with vbokonly and then DEPARTMENT_NBR.SetFocus, but after the message displays and enter is hit for the ok the cursor jumps to the DEPARTMENT_NAME on the current line. Is there a way to get the SetFocus to work properly on repeating items like this? I can never seem to get them to perform the same as they do on non-repeating items.
Thanks for your help.
HERE'S THE EXACT CODE: If IsNumeric(DEPARTMENT_NBR) = False Then If DEPARTMENT_NBR <> "00" Then MsgBox "DEPARTMENT NUMBER must between 01 and 99.", vbOKOnly DEPARTMENT_NBR.SetFocus GoTo DEPARTMENT_NBR_EXIT (exits the paragraph skipping other checks) End If End If
Also, is there a way to look at a specific item in a list like that? IE. I want to reference the 3rd row's DEPARTMENT_NBR. Thanks.
I'm trying to set up a query where it returns me only the items that are repeated.
ie...I have a table that has clients that are one time clients and other that are repeated. Each client has a client number. I would like to get only the clients whose client number appears more than once. Is that possible?
Hi I want reduce one minute from my date field. My date field looks like this: 17.3.2006 3:57:00 and I want it to look like this:17.3.2006 3:55:00. I have tried to make it like this: DATEADD( minutes, -2, [StartTime]) AS NewStart. StartTime is date field. But this doesent work. Could somebody tell me how to reduce one minute from date field??
Once the selected item had been entered, i want the Current_Balance of previous entry of that item to be forwarded automatically in Previous_Balance field. Is it possible? I have at least 100 types of items.
Access Database in which i download the table from an Excel file.I am looking to create a query with certain criteria but did not have any luck recently.
1) I will need the query to show <Short code> that are only unquie to Korea.That means shortcode with a count of 1 belonging to Korea only.
2) I will need the query to show as a single line item <Shortcode> that appears two times under the country Korea. For example short code CB01406 is shown as two rows, i will like it to only show as a single row.
3) For all other shortcodes which exist in China as well as in Korea or China only. I will like them to be hidden in the query. That means not shown at all.