I have a database where we regulary import excel data to generated from a form sent to our clients. The excel data that we import normally holds around 10 to 40 records at a time.
The current process I use to do this is to run a macro that creates a new table (tblImportForm), and then run an append query to append those records to our existing main table (tblJobDetails). I should also mention that the macro first deletes the existing tblImportForm before creating a new one with the same name with the new data.I have used this method rather than appending the data straight into the existing tblJobDetails as I found I came into more problems with generating IDs etc.
What I want to do is have a report come up after the data has been imported/appended, that lists the new data imported with the new record IDs generated in the main table tblJobDetails. It would be easy to do this is if I only needed the data or could use the IDs in the first table I import to, but the idea of the report is to give the user the new IDs (PrimeKeys) from the second table that the data is appended to.I could maybe do a count of records in tblImportForm and then produce a report using a query from tblJobDetails that pulls that number (the count) of data from the last record backwards? But I don't know exactly how to do this.
I'm wondering whether it won't produce only these records because of the design of my report (attached), with the Site Name under the SiteID Header...?? I wouldn't have thought it would matter, but...
My aim is to produce a report - using a drop down list (of customers from customer table) so that users can look at customer history.
I have created a form with a drop down list of customers - from customers table. I have used this to filter the results for a query on the orders table (gives me a history of that customer). All this works perfectly. I get all the transactions for that customer.
My question - is there a way I can create the same result but in a report format instead of the datasheet format. I.e the user will be given the data in report format when they select the customer from the drop down list.
I have produced a query, which counts the number of records fulfilling each set of criteria, but I can't now convert that into the matrix presentation.
I have a attached a copy of what is produced currently through Excel.
Basically each count on the query represents one of the boxes within the matrix. For example if the record Impact is 1, and the Likelihood is 1, then it would be counted within the bottom left hand box.
What I would like to know is this: I have produced a basic members table containing individual's details etc. How do I now produce an individual report for selected members (single page per member), in which I can determine which of the table details are included?
Hello, hoping for some help on the following scenario:
I need to produce a query which results in one record for each part number.
In a sample table of purchasing history (tblPODetails): Fields: PartNumber, Description, Cost, DatePurchased
We have purchased any given part number numerous times over the years. From time to time, descriptions have changed, as have our cost and of course the date received.
I am attempting to create a query which gives me a list of part numbers with no duplicates, showing the part number, description and cost by max date received.
The resulting description field would of course be the one in use for the latest received date.
I am not having much luck. When I add more fields to the totals query (other than part number and max date received), I get duplicates. I know this should be simple for me...
I'm trying to produce a report which uses a static deisgn and does not change. For example,I require in the detail of the report a table say 3 * 8 ....and in the report I need the data to fill each one of these cells. However I may not have 8 records , therefore I need the remaing columns/cells empty - the design of 3 * 8 must not change... at the moment I have a dynamic design so if i have 3 records I have 3 *3 table leaving a massive gap on the report.
I am very new to access, any way that I can have a popup box appear when I ask to produce a report and I can type in a parameter of my choice and it will produce a report based around that parameter?
I have a table that is directly linked to an excel spread sheet that is updated each week external to the database. I have to produce reports on the data contained in that table.
I have already produced reports that look for specific number and those reports are produced automatically.
I was wondering if there was some way I could open a form and type a number into a text box and it would produce a report around that number. e.g. "list all engines below X margin" and I can type any number in representing X and a report would be produced.
I have a table that is linked into access 2003. This table is updated by personnel in another location and I have to run a weekly report on engines that are below a certain performance level.
The column heading is MGT Margin and I have to list all of the engines that are below 20 degrees.
Can I run a query that looks at this table and produces a report of all the engines that are below 20 degrees?
I currently have to cut and paste each engine from the updated spread sheet every week onto a separate spread sheet and import that into access. If a query can be used to do what I am after I can use similar principles in other reports I have to run.
I am looking to call two different queries from report wizard to produce report. Getting error message what to do in this situation as both queries are important as i have to pick all records from query A and just one record from query B any other option to get this in report.
I have a 2 databases exactly the same I need to append the records from one of them to the other. The only tables that need appending are "Transactions" & "Details"...that's it.
Detail Tbl: DetailID (FK to TransID) DetailDate...
The problem is how do I combine them because TransactionID is an Autonum? If I append one Transaction table to the other one (without the autonum of course), a NEW autonum is assigned then I can't link the DetailID's when I append the detail tables together.
What to do? I only have 44 total transactions to worry about so it's mostly curiousity.
I have three tables, table 1 'ServerTest' contains 5 records which should not change, table 2 'Region' has 40+ records. using an append quary all records are combined into table 3 'Junction1'
my problem... the Region table will change over time and new records will be added. how do I append the new records only to the junction1 table?
this is my append quary; INSERT INTO Junction1 ( RegionID, TestID, TestNumber, TestDescription ) SELECT Region.RegionID, ServerTest.TestID, ServerTest.TestNumber, ServerTest.TestDescription FROM Region, ServerTest;
this is a query I'm running, which I think is quite simple....why does it not bring anything across?
INSERT INTO tblRunItems ( SiteId, RunId ) SELECT tblsites.siteId, forms.frmRuns.runID FROM tblSites INNER JOIN (tblRuns INNER JOIN tblRunItems ON tblRuns.runID=tblRunItems.RunId) ON tblSites.SiteId=tblRunItems.SiteId WHERE ((tblSites.CollectionDay)=forms.frmRuns.runDay) And ((tblSites.CollectionZone)=forms.frmRuns.runZone);
i'm new with access and was wondering if i can get some help with an append query?
I'm getting an error message that state the following below:
*Microsoft Access can't append all the records in the append query. Microsoft Access set 36 field(s) to Null due to a type conversion failure, and it didn't add 0 records to the table due to key violations, 0 records due to lock violations, and 0 records due to validation rule violations.
This is my append query below:
INSERT INTO CustExportHoldCiti ( Custodian, CustAcct, Ticker, Cusip, CurrCode, SecurityName, Shares, CustCost, CustValue, POI, OriginalDiscount, HoldingType, Loc, Expr1 ) SELECT DISTINCT [CitiBank Current Holdings].Custodian, [CitiBank Current Holdings].[Account Number], SecSymbolsAll.SecSymbol AS Ticker1, [CitiBank Current Holdings].CUSIP1, [CitiBank Current Holdings].Curr, [CitiBank Current Holdings].[Security Description], [CitiBank Current Holdings].Shares, [CitiBank Current Holdings].CustCost, [CitiBank Current Holdings].CustValue, [CitiBank Current Holdings].POI, [CitiBank Current Holdings].OriginalDiscount, [CitiBank Current Holdings].HoldingType, dbo_Locations.Location, [date] AS Expr1 FROM [CitiBank Current Holdings] LEFT JOIN (SecSymbolsAll LEFT JOIN dbo_Locations ON SecSymbolsAll.LocationID = dbo_Locations.LocationID) ON [CitiBank Current Holdings].Ticker = SecSymbolsAll.SecSymbol WHERE (((SecSymbolsAll.SecSymbol) Is Not Null));
I can't figure out what is wrong with it. Thank you for the help.
I have a query that will pull records, and append them to the table. The only problem is that not only will it pull all the "new" records, but also all the "old" records. I just want to append the records that are not already in the table. Any ideas? I have tried using the primary key, but then it dosnt import any records. There will be say 1850 records. And out of the 1850, 1 would be a new record.
I'm not sure if this is the right discussion to ask this, but it seems the closest to my question, so I apologize if i'm mis-posting.
I'm doing a "run-time" insert into a table, from the VB Script. A button pops up an InputBox, and the returned string is added into the table, via a SQL command. However, upon execution of the SQL command:
st=InputBox("New Entry") call DoCmd.RunSql("insert into table1 (field1) values ('" & st & "');")
I get a warning dialog pop-up from access, informing me that it's about to append 1 row(s) into the table. Is there anyway to deactivate that dialog warning box so it doesn't pop up every time i use an insert into statement?
can someone please help me with this problem. currently im working on a student management system with MS ACCESS. Im stuck on the create_assessment_form that allows you to query previous assessment results or to create a new assessment. It has a subform that shows the assessment results of all the students relating to a selected assessment_ID.
The query of the results are working properly, But I need everyone's help for the creating new assessment part.....
When I create a new assessment, I have to choose a course_ID the assessment is relating to. >>>Question<<< How do I make it so that it will generate a list of assessment_results record for each of the student relating to the new assessment?
maybe my explaination is a bit messy. Please let me know if you do not understand my question. But I really need an answer to this question urgently!!!
I'm making a database for tracking purchase orders for contract workers in my department. Business rules dictate that a PO is created for each unique Contractor-Project instance.
I pulled a report (.csv format) of weekly timesheets from the company's project management database system. Some of the fields include: EmpUserName, EmpID, ProjectID, ProjectName, WeekEndDate, Hours. I linked to the file in my database and ran a query on it without WeekEndDate so that I get the total hours each contractor has worked for each project they've billed to. I created a table called tblContractorProject and appended the records to it. I'm going to be pulling this report each week so I can see if any contractors start billing to any new projects so I can create a purchase order for it. Is there a way to write an append query that can recognize if the EmpID and ProjectID combination is new and doesn't already exist in the table?
The goal I am trying to accomplish using VBA within Access 2010 is to append/add certain records to another database (Customer Service) from my database (Client Request). I tried using a macro to append then update records in my database but there is a "lag" time as to when the information is updated in the 'Customer Service' database causing my updates not to occur.
I have a single table database for inventory. Every 3 months or so, some records get changed or updated. My manager wants to keep an archive of all of the records that have been changed so we can go back and look at an history of all of the records. So, My thought is to create an "Archive" table, appended all of the current records to it. Then, when changes are made, create an Append Query, or what ever works, to copy just the changed records from the Main to the Archive table. There are only 200 items in the inventory so it is not a large database.
I am trying to implement an update query per the instructions at http://support.microsoft.com/default.aspx?scid=kb;en-us;127977 to append records from table2 to table1.
I would like to add a criteria here saying update (and append) only records that match the criteria table2.dateofreview <= #12/17/2006#
This does not seem to work. The same query works without this criteria.
To test this, I created just one record in table2 (table1 is blank at this time) and manually entered the dateofreview as 12/17/2006 and tried to execute the query. It is not able to show any records.
I have a table which keeps a record of days off sick.
The table has the field [First Day] = the first day they were off sick, in a dd/mm/yy format
and a [Last Day] = last day they were off sick, again in the same date format.
I need to have a query that will produce the actual day, and all of the days in between; so - if the First Day is 25/10/06, it will produce "Wednesday" and then the last day is 27/10/06, it will produce "Friday" But, I also need it to produce "Thursday" (which would be the 26/10/06)
I have 2 comboboxes to select a product no, and a unit of measurement
I have a materialprices table with unique records, keyed on productno and unitno, so i should get either one or no records returned from the following SQL
However, if i compare the values of the returned records with the search values and the tests, it shows a difference, although the difference is then showing as zero when I evaluate it.
(I found this because initially I had written the code using seek, and was trying to ensure I had located the correct record)
I have actually got round this by testing the abs difference as being greater than a minuscule value.
ie If abs(rs!pmpprodno - cboProd)>0.01 Or abs(rs!pmpunit - cboUnit)>0.01 which does not produce an error
I could understand this if I was testing real numbers, but these are all integers - any ideas anyone?
so ignoring the dims - ignore any typos - the code compiles and executes properly - its just the equality test thats the problem
function lookupmat as boolean
strsql = "select * from tblmatprices where [pmpprodno] = " & cboProd & _ " and [pmpunit] = " & cboUnit
Set rs = CurrentDb.OpenRecordset(strsql)
If rs.eof Then lookupmat = False rs.Close GoTo exitproc End If
'having used the above where clause to find an item, this test now seems to produce a difference, even though the figures are the same! If rs!pmpprodno <> cboProd Or rs!pmpunit <> cboUnit Then MsgBox ("Unexpected - There is a difference " & vbCrLf & _ "cboProd = " & Format(cboProd, "###.0000000000") & _ " Lookup = " & Format(rs!pmpprodno, "###.0000000000") & vbCrLf & _ "cboUnit = " & Format(cboUnit, "###.0000000000") & _ " Lookup = " & Format(rs!pmpunit, "###.0000000000") & vbCrLf)
'and now both of these tests are showing a difference! of zero If cboProd <> rs!pmpprodno Then MsgBox ("Prod Was different " & cboProd - rs!pmpprodno) If cboUnit <> rs!pmpunit Then MsgBox ("Unit Was different " & cboUnit - rs!pmpunit)
into the following data format (13 columns in total, with 7 rows):
Project Jan Feb Mar Apr May Jun Jul ... Dec ------------------------------------------------------------------- A............100.................................. .......................................... B.....................200......................... .......................................... C.....................300......................... ........................................... D...............................................40 0......................................... E...............................................50 0......................................... F...............................................60 0......................................... G................................................. .........700...............................
Assuming today is January 1st 2007, the 2nd column must starts with current month.
If someone can post me the SQL, or give me some tips or direct me to an article on how to achieve the results above..it'll be most appreciated.
Here is my issue. In a table with an Auto Number index some records have been deleted. I have been able to recreate them along with their original auto number. The problem is that I do not know how to append these records forcing the original auto number. I have tried changing the auto number field to a number field in the table, this works except I cannot change it back to auto number.
I am sure I’m not the first with this question or issue. I did search through a couple hundred entries about auto number before I posted this question.
