I have an issue using the Simple Query Wizard in Access.
I am attempting to Group by the field 'HouseName' and to group by the Max 'Area'. However I also want to retrieve from the query the County which is associated with this.
I have included a sample table below. I wish for my output table to be as follows;
HouseName: County: Area:
Park 1 A 100
Park 2 C 78
Park 3 A 70
(Where Park 1 is HouseName attribute, A is County attribute and 100 is Area attribute)
Sample Input Table:
HouseName: County: Area:
Park 1 A 100
Park 1 B 60
Park 1 A 85
Park 2 C 78
Park 2 D 34
Park 3 A 70
In SQL View my query looks like this;
SELECT TableExample.[HouseName], Max(TableExample.Area) AS MaxOfArea
FROM TableExample
GROUP BY Table.[HouseName];
I've set up a database for product tracking. It is going to be used by several users at one time. I'm going to implement it in stages as inevitably there is going to be some fine tuning to be done etc. I have a few questions with regards to editing.
1, I presume that I cant alter the database while it is being accessed by others?
2, Is it better to alter another copy of the database and the import the data being generated and then copy it to back the server in one go?
3, If so how do i do this as I’ve had a trial go and failed miserably?
4, Is there another way of doing this minimising the down time of the database?
Being relatively new to access your help would be gratefully appreciated.
I am creating a database for a company that sells a product with a variety of options.
They have all their previous orders in a works spreadsheet file. Each customer has their own file with every order for the past 15 years. There are probably about 1.5 million records.
The company wants all those 1.5 million records accessible in their access database.
I've brought in about 20 records for the history and they can run a query to see a customer's past orders by their account number.
My question: Should I put all the history into one gigantic table or would it be best to try and create separate tables for the history? (Maybe history by state.)
The history table then will take the new orders each year and add to that table.
(Eventually, once the database gets done I'm thinking we'll have to step up to SQL but not sure.)
i inherited a database created i believe in access 2000 everytime i need to update a report or what not i must find a machine runnning 2000 how do i go about working on this database with access 2003
I have a form with a "WorkerID" field. Once the worker enters his ID once, I want the same ID to show up for all the subsequent records so that he doesn't have to type it over and over again. Is there any way to do this? Sorry if I sound totally new to this (I am)!
How do I maintain referential integrity between a main form and a subform, each based upon different (but joined with integrity enforced) table?
Here's the situation: I have two tables: tblContracts and tblPayments. tblContracts has an autonumber field called IDKey as its primary key. tblPayments also has an IDKey field (Integer datatype). The two tables are linked in a one-to-many relationship on the field IDKey with referential integrity enforced.
I have a main form based upon tblContracts (the "one" side of the relationship) that has an embedded subform based upon tblPayments. the two forms are linked Parent/Child on the IDKey field.
Here's the problem: If a user goes to a new record in the main form, it allows them to enter information in the subform without entering information in the main form. This means that a new record (and its corresponding autonumber IDKey field value) does not get generated in tblContracts and I have an orphan record in tblPayments that is not linked to any record in tblContracts - which violates the referential integrity that is supposedly enforced between the two tables.
Any guidance on how to deal with this would be greatly appreciated.
I have an application that is used by individual teachers to generate reports for central admin. It is not secured except I have locked out access to all objects, Navigation pane is hidden, etc to protect the integrity of the tool. I must also maintain this as changes are propagated. I am trying to find a way to upgrade forms, functions, queries, etc without manually having to unlock and relock every db for each school site when changes are required. I have tried importing and exporting from a master db, turning objects on and off by recognizing my password, splitting code and data (db's are on flash drives and path changes every time they are inserted, many teachers cannot handle refreshing table links), I have tried writing code to import the changes at next startup, etc. It is written on Access 2003.
I have a table for logging experience for employees related to a given requirement.
The requirement is that any employee, to maintain proficiency ("be current") , must maintain an event count of minimum 5 within last 3 months. Or else the employee is not current and other measures must be taken.
I want to know when the currency expires for each employee (= at which date does the employee no longer have a count of minimum 5 within last 3 months).
I need to create buyer and seller invoices within my access 2003 databases, my sister in law runs an antiques auction house and I'm working on a database to capture all their information.
I've created a 2 queries (a buyer invoice and seller invoice), show all unsettled items with the relevant item information and fee's.
However, I would like it to automatically allocate an invoice number, and store the information back to a table (seller and buyer ID, total number of items on the invoice, total fee etc)
I'd like store the invoices as well, so within the customer record form, I can include a box showing a list of the buyer invoices on one side and seller invoices in another (not all customers are buyers and sellers).
Once I've issued an invoice, can I automate it to show that item as then as invoiced?
Can the invoices be editable at all?
When they come in and settle their account, I also need to feed this information back in as well.
Can i arrange an invoice for seller's particularly, to show all sold items, and the fee's associated with them
Returned items as well as any associated fee's with them?
I have been looking at the Northwind example.
I see how they have an orders details table and orders table, I could replicate this.
I could have an Buyers Invoice table and Buyers Invoice Details table, but how do I generate an invoice, pull the next Invoice number from my (currently blank) invoice table, and pull in the outstanding items for that buyer for that specific auction date (I have a query), and then populate back information from the invoice?
I like the fact that their order form is editable, do any changes go back to the query, that then populate the invoice when you click print invoice?
I need to be able to (at the end of the auction) pull all items from my item log, for that buyer, and print them an invoice, automatically saving all the invoice details back into my database.
I need be able to automatically flag the items in my items table, as invoiced and pull the invoice number in?
I can see how all the Northwind tables, queries, and forms relate to each other and subforms, but I'm not sure how to actually create an invoice, get invoice number, merge with my query, and then feedback in.
I am trying to make a basic database system to track orders and contacts and the like for my small business. I have created a series of databases that look pretty standard, something like this example:
I want to create a form that I will open and complete every time I process an order. I want to fill out both the customer info and the order details at one time.
Assuming that the "CustomerID" in the "Customers" table is set to Autonumber in order to create a unique ID, how do I keep from creating multiple new "customers" every time I enter a new order regardless of whether or not that customer already has an ID number from a prior order?
It seems that I would need to make some sort of Macro that would say something like, if the "CompanyName" entered is already listed in the 'Customer' table, use the existing CustomerID, if it is not listed in the Customer table create a new CustomerID.
Should I be looking into Macros, and if so should the macro be built into the form or the database?
I've been using MS Access 2007 for years to manage some Excel data. running some queries etc. Just recently I've been encountering problems when importing data into an existing table. When I do it now, I get a "Subscript out of Range" error. To troubleshoot, I imported into a new table and when doing so, the fields no longer match the column order of the spreadsheet. They all get imported but appears in a different order. I think this is why I am getting the error message. How can I go about ensuring that the data gets imported properly into my already existing table? My fields in "Design View" will match the order of the Columns in the Excel spreadsheet.
I am creating a 2 level report to confirm an order. Main report already created, runs successfully called as subform/subreport under "OrderDetails" form. Linked to master using Order.ID. There are two versions of the confirmation report that have different layouts for different program types.
The hangup comes when I try to add a "Class Dates" subreport. It lists dates of individual classes and Skip dates. I have created the subreport as "srClassDates". When I add it to the main report, it lists the records. However, when I try to link it to the Main report, an error message box appears with the "object variable or With block variable not set".
I have tried rebuilding both the main and subreports, rebuilt the query, have not found anything that changes the result.
Linker has been working successfully on other subforms. Report with groupings works fine, but I need data from 2 tables both linked to order.id.
Error 91 - Object variable or With block variable not set
I am getting this error telling me that an object variable is not set.
I know which variable it is but when I step through the debugger it sets the variable and all is fine? Issue is that public variable of a class is not getting set when the VBA Editor is not open?
This code runs fine the FIRST time, however trows up a message the SECOND time it is run.
The error is on the line ".Range"
I am trying to sort records which have been exported to Excel.
Dim LR As Integer LR = 5 Set xlApp = CreateObject("Excel.Application") xlApp.Visible = True Set wbRef = xlApp.Workbooks.Add With wbRef
wbRef.Activate .Worksheets("Sheet1").Activate With ActiveSheet .Range("A2", .Cells(LR, "O").End(xlUp)).Sort Key1:=.Range("C2"), Order1:=xlAscending, Header:=xlYes End With end With
I have two reports running off of the same crosstab query. I copied one report to make the second report, then modified the second report to change the background of column fields satisfying certain conditions. These lines of code were added to the Detail_Format section, in color below. The report with the extra code lines does not error--the original report errors.
Code: Option Compare Database ' Constant for maximum number of columns EmployeeSales query would ' create plus 1 for a Totals column. Here, you have 9 employees. Const conTotalColumns = 11
[Code] .....
If I say OK (rather than debug) after the error message, I can then click the button for the report again and it runs without complaint. And, as I said, the report with the added code never errors.
I have an ordering db that utilyzes the typical Form / Subform layout. I would like to be able to delete a record from the Main form and maintain the same postion relative to the other records. For example, if I am scrolling through the records and want to delete record #45, After I delete it I would like to be able to have record #44 visible to the user.
I am currently using (2) SQL statements to delete the records from the main Order table and the Order Details table which are linked without Cascade Delete Related Records being on.
I had set-up a recordset procedure to try and find the bookmark set before deletion but the only way I can get the sub-form to not show a blank form (no controls visible for the record just deleted and the #deleted in controls on the main form) is to requery after the delete, which loses the bookmark. If i place the ReQuery at the end it displays the records properly, (without the blank record) but returns the record to the first record. I have tried turning off any sorting references to OrderBy, etc. but it still returns to the first record.
I have also tried using "DoCmd.RunCommand acCmdDeleteRecord" but it doesn't delete the record from both tables.
I have a networked database. It is accessed with computers that have both Office 2002, and office 2003. I get the following error message on computers with Office 2003:
"Object variable or With block variable not set"
It happens occasionally and the problem generally fixes itself so I dont think there is anything wrong with my coding.
And when it happens on the computers with office 2003 the ones with office 2002 can open the database fine.
The following block of code was working great until I came into work this morning. This routine is called from the OnClick event of several combo boxes. When it does, I recieve the error listed above (Object variable or With block variable not set) on line:
If Me.cbxAss_Filter <> "All" Then
Any ideas? I'm running on no sleep in the last 24 hours so I'm sure I'm missing something simple
The code itself checks the contents of a form and builds a string of conditions which I apply to the Form's filter property to filter records.
Code:Public Sub CreateFilter() '************************************************* **** 'Name: CreateFilter 'Purpose: Generate a string to filter the form 'Inputs: None 'Outputs: None 'Instigates: Me.Filter ' 'Updated: 11/30/06 'By: Chris Lounsbury '************************************************* **** 'Vars Dim strFilter Dim lngLength As Long strFilter = Null strFilter = "" 'Each filter box has its own check for contents If Me.cbxAss_Filter <> "All" Then strFilter = "assigned = '" & Me.cbxAss_Filter & "' AND " End If If Me.cbxAction_filter <> "All" Then strFilter = strFilter & "action = '" & Me.cbxAction_filter & "' AND " End If If Me.cbxStatus_filter <> "All" Then strFilter = strFilter & "status_rsrch = '" & Me.cbxStatus_filter & "' AND " End If If Me.Combo34 <> "All" Then strFilter = strFilter & "rims_flags = '" & Me.Combo34 & "' AND " End If If Me.cbxAAMB_filter <> "All" Then strFilter = strFilter & "aamb = '" & Me.cbxAAMB_filter & "' AND " End If 'Check if filter string was built If strFilter = "" Then Me.FilterOn = False Else 'Determine length of Filter String 'minus the trailing ' AND' lngLength = Len(strFilter) - 5 'Chop off ending ' AND' and set the form filter If lngLength <= 0 Then Else Me.Filter = Left(strFilter, lngLength) Me.FilterOn = True End If End If 'debug 'MsgBox (strFilter) End Sub
I get this error message, " object variable or with block variable not set", when I'm in the design view of a form wanting to use the command button wizard. Using a brand new database/blank I imported a table from my database in question. I then tried to make a new form and put a button on it. It worked for awhile but then when I close the database and reopen it to add another button to the same form, I get the error message again. It isn't a problem that is specific to my homeschool database. It affects all the database I use/maintain as well as any new "test" databases I've made trying to figure out this problem. I looked at references before and after the error occurs and there is not difference. I've also looked at code but don't see anything code that requires variables to be defined. In the case of the test database with one table and one form there is no code to look at until I make a button before exiting and even then, I don't see anything weird.
I would appreciate any help you can offer.
I've attached the original database I was working on when the problems began.
Is it possible for a database program to adjust security levels on certain reference libraries when installed on ones computer, thus making all other database act up that are using those libraries?
I have created an invoicing system for a CD shop There is a transaction table which has each individual cd sold as a seperate transaction. Each transaction has an order number, so there can be more then one cd sold per order, but they all still have their own record in the table.
im now invoicing each order by mail merge based on a query that finds all the details on every transaction but wht ive found is that the mail merge puts each transaction onto a different page even if its from the same order number as another.
does anyone know how to group each order in the query so that all the items in one order come in a single invoice?
Hi, I have a query which numerous fields, and I need to make a report based on the query. However I need to group 3 fields in the query and press the sum button on 2 of them, Qty and Total Price (which is qty*price)
I need to do this so in the report when a particular wine is purchased more than once, instead of repeating the peoples name who bought the wine, it will only show 1 and automatically add the rest to the total price.
I dont know how to group within a query, can someome please tell me how? Thanks.
Hi to all; I have one code of 6 digits; each digit refer to a group; first 2= product group; beverage; live animals,….itc (24 product groups), the 3ed digits= food product, the 4th-6th digit= detail product group; vegetables, fruits,….,the rest of digits refers to product name, carrot, apple,…itc , example 070511 How can I use the query to sum the product value at different group level; example the first 2= product group, ..? Do I have to split my code to 6 cods to do my calculation? Thank in advance for help majed
I have created an invoicing system for a CD shop There is a transaction table which has each individual cd sold as a seperate transaction. Each transaction has an order number, so there can be more then one cd sold per order, but they all still have their own record in the table.
im now invoicing each order by mail merge based on a query that finds all the details on every transaction but wht ive found is that the mail merge puts each transaction onto a different page even if its from the same order number as another.
does anyone know how to group each order in the query so that all the items in one order come in a single invoice?
Hi all I am trying to find a way of finding the number of a group of sessions as a percentage figure. e.g. total number of clients attending 1-3 sessions = 20%, 4-6 =15%, 7-12 =21% 1-2years = 8%, etc. and entering this calculation in a report.hope you can help. Dave
Question on grouping within Access Reports: A simplistic view of the report I'm trying to generate is as such:
Company Name - Company Description - Employee
I am grouping by Company name, and I am hiding duplicates of Company Description because they can be long. I also have the Company Description as Allowing to Grow.
The problem is that the first record gives the company, the description, and the employee name on the first line just fine, but the second employee name won't be listed until the Company description ends. When the description is about a paragraph long, the other employees look very seperated from the initial record.
