Retrieving Rows From Multiple Table To Fill MS Access Report.
Sep 20, 2006
Hi,
I am using VB.NET 2003 and MS Access XP for a desktop application. While developing the application we have a reached a situation where we want to print a report which retrieves records from four tables. Till here it is easy to think that it can be done by a simple SQL JOIN query, but following is the complexity:
The first table stores a single row.
The second table stores multiple rows related to the Primary Key field defined in Table One.
The third table stores a single row related to the Primary Key field defined in Table One.
The fourth table stores a single row related to the Primary Key field defined in Table One.
The above SAVE RECORD option is performed when a user fills a Form of my application. As stated above, all the four tables are inter-related with a Primay Key field (TNo) defined in table one.
I also have a MS Access Report that will print information retrieved from all the four tables. The Report has some of the fields from each of the above table. The SAVE operation is performed in this way:
(1) A unique TNo is generated for a new record that is about to be created.
(2) All the entries are saved in their respective tables (mentioned above.)
(3) An access query will fetch the records pertaining to this TNo from all the tables to fill the report.
I want to know how to write such a query when I have to fetch multiple rows of a table in between. Is there any way that I can pass the TNO as a parameter to this query that is saved in MS Access?
I am trying to write a query that selects multiple rows of a table and puts those into a single string with a comma delimiter.
I want it to select all rows after a specific word is found in the row above and then stop selecting when another specific word below the last row is found.
Example below: >>>
The data i am using is very unorganised and has not headers, so I have to try and specific lines in order to allocate them to a specific user.
Hopefully someone can help me this (and hopefully the solution isn't staring me in the face)
I am trying to export data from a cell in a worksheet to a field in an Access database that I am building. The database has two tables, tblCompanies and tblQuotes. It works through a macro accessed by a command button, which should export the total of a quote into the field Quote_Price in the tblQuotes table, which the users will access through a form. The problem is that if I export the data from Excel, I get an error message saying, "You cannot add or change a record because a related record is required in table 'tblCompanies'. The code looks like this:
Sub NewQuote()
Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long ' connect to the Access database Set cn = New ADODB.Connection cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _ "Data Source=C:Documents and SettingsDJEdwardsMy DocumentsTest Foldermarketing.mdb;" ' open a recordset Set rs = New ADODB.Recordset rs.Open "tblQuotes", cn, adOpenKeyset, adLockOptimistic, adCmdTable r = 55 ' the start row in the worksheet Do While Len(Range("I" & r).Formula) > 0 With rs .AddNew .Fields("Quote_Price") = Range("I" & r).Value .Update ' stores the new record End With rs.Close Set rs = Nothing cn.Close Set cn = Nothing End Sub
I can import the cell contents from Access with no problems, but I would like the user to be able to do it from Excel to simplify things.
However, when I drag the field lists into the report Im getting a new report entry for each drill eg.
Smith 18/9/14 0 - 100
Smith 18/9/14 1 - 150 etc.
Any way to get all drills to show in the one report entry per person? So all data for Smith is in the one report or report section, and then all the data for Doe.
In a situation where I imported an excel file with so many columns and split them into two temp tables and they are linked using a key.
the data has a fixed part lets say
Field1....Field2.....Filed3.....Field4...then Field5.....Field6.....Field7....Field8 is the same data range as Field9...Field10...Field11...Field12. I would want to split this data into multiple rows like this
Field 1 Field2 Field3 Field4 Field5 Field6 Field7 Field8 Field 1 Field2 Field3 Field4 Field9 field10 field11 field12 and so own...
I'm trying to add up all the emails in a single string , but I can't access the records one after the other. The best I can do is display the currently selected record's adress ( The one with the black arrow in front ) . I tried browsing through the Fieldname.Value propertie ( in this case Me.Courriel.Value ) . I though I could cycle through all the Controls in the form , but Me.Controls.Count returns 3 , so I figured all the records must've been embedded into one control. Can anyone help me :( ?
(Sorry if this is a documented question , I've tried searching but couldn't find anything )
HELP! I’m working for a charity in a small town Tanzania and there is not a soul for miles who knows anything about access.
I have created a database to track requisitions of Office Supplies. I have table full of the requisitions which contains multiple rows for each item we have in the store and the quantity requisition on each occasion.
What I want is a query that will calculate the total quantity of each item requisitioned in a month. Is there an easy way to do this??
I have three tables, with the following data (fields separated by "-" here): Unit - Customer Number - Customer Name - Type of A/R - Total A/R Unit - Customer Number - Customer Name - Type of A/R - Total Overdue Unit - Customer Number - Customer Name - Type of A/R - > 90 Day Balance
I would like to combine these tables into one table with the following field names: Unit - Customer Number - Customer Name - Type of A/R - Balance
When I append the data, it comes out like this: Unit - Customer Number - Customer Name - Type of A/R - Total A/R - Type of A/R - Total Overdue - Type of A/R - > 90 Day Balance
I would like this new table to include all rows from the original tables, sandwiched on top of each other. As far as I can work it, the Append Query only adds the data as add'l columns; not as add'l rows. I tried changing the field names entirely, so that the final field in each table is called "Balance." However, I still get the same result as above - the columns are added, instead of rows. I swear there's a way to do this, I just can't remember it!
And I'd like to convert it to this: Member_IDDiagnosis - 1Diagnosis - 2Diagnosis - 3Diagnosis - 4 10000 HypothermiaFrost Bite 10001 Fatigue DehydrationExhaustion 99999 Exhaustion Hypothermia
The columns don't line up well but I am looking for each diagnosis to move into one of the 4 columns, depending on whether it is the first, second, third or fourth diagnosis associated with the member.
I have a query that has multiple IDs and different information in numerous fields. For example:
ID Field1 Field2 Field3 1 x 1 m 1 b
I need to find a way to concatenate the data so that it shows the information like this:
ID Field1 Field2 Field3 1 x m b
I'm working in Access 2010.
Yes I know this isn't the best way to set up a database, but I'm trying to make fixes to an already existing database that I can't go back and change the way it is set up. I can only find work arounds to accomplish what I need.
So I want a way to update Status of my rows according to numbers list. For example I want to update Status column for multiple numbers to become Done .
Simply I want to update "Null status" to become "Done" according to its numbers according to this list
Code: 1234 53 546 767 2135 6657 43 34
Something like this
I tried "update query" but I don't know how to use criteria to solve this problem. In Excel I did that by "conditional formatting duplicates" -with my number list which I wanted to update - Then "sort by highlighted color" then "fill copy" the status with the value...
I'm trying to create a report that has certain rows filled with a background color. In excel, the fill color is complete across the row. That is, there are no gaps between cells. In Access, each individual "cell" or field is filled with the color but inbetween each cell/field there is white space. I tried changing the control padding to "narrow" but there is still too much white space. Changing control padding to "none" changes the alignment of the cells to look off. They aren't exactly lined up in a row anymore. It looks terrible.
Ok Ive looked and looked and read a multitude of threads to no avail. Please help me on this one.
I have imported a table into Access, I had no control over the original format of the table so Im stuck with the data as it is in that original doc.
So as it sits now in access.. index mnem client name -------------------------------- 1 310055 blah foo 2 blah2 john 3 blah4 joe 4 310099 grr12 bob 5 blah2 john
etc. * about 50,000 literally in size.
Looking at the empty cells in the 'number' column I need each of those to be filled. to end up with: index mnem upin name ------------------------------- 1 310055 blah foo 2 310055 blah2 john 3 310055 blah4 joe 4 310099 grr12 bob 5 310099 blah2 john
I did this once in VB/excel but it was prohibitively slow. Im positive there is a better SQL/Access solution but just cant quite get it. My best effort to date is: DLookUp("[mnem]","upin","[index]=" & [index]-1) criteria mnem Is Null In an update query updating mnem.
Downside all I get is say there is a block of 4 empty spaces in a row before the next mnem comes along, only 1 cell will be filled per run of the query.
i.e. index mnem upin name ------------------------------- 1 310055 blah foo 2 310055 blah2 john 3 blah4 joe 4 310099 grr12 bob 5 310099 blah2 john
I figure #3 is somehow looking up at #2 before its updated and taking that empty value. ..just cant figure a way around it though.
I know this has been done before but no matter how much googling and forum diving Ive done I havent found something to specifically address this.
I have a table with the following columns: Task, Visa type, time it takes to perform the task. There are several taks that are performed for all visa types. I want to create a form to enter data to the table in which for the field visa type I have a list box that can allow multiple values, however, I do not to create a single line with the task and on visa type all the types of visas selected. I want to create a line for each type of visa with the information introduced.
I don't know if this is possible, the reason for which I want for the form to create several rows depending on the visas types is because then I have a query that sums all the types of visas. Can this be possible? I don't want the people to introduce manually directly to the table the data and also that for the same taks they have to enter manually 50 rows with values. I want it to be more simple and easier.
I have a VB6 form that I want to fill with data from a field in an Access DB. I get the connection and the first field, but want the whole column to show up as the combo box items so that one can be selected, then when saved, populate and/or update another Access table. Can anyone help me cause the whole column to display instead of only the first record? TIA--Ed
I am trying to find the latest date in a table where the dates are in 2 separate columns and multiple rows. (there are business reasons why there are 2 dates per row they represent different but comparable activities)
I have a table "Assessment tracker" with the following structure
Name Type Candidate short text Unit short text EV1 Date Date EV2 Date Date
My Data:
Candidate Unit EV1Date EV2 Date TH1 10 07/05/2015 25/05/15 TH1 10 07/05/2015 07/06/15
I have a query "Candidate AC Dates" that compares the 2 dates EV1 and EV2 and outputs a 3rd column with the latest date.
It does this by using a function shamelessly copied from the web somewhere...
Function Maxdate(ParamArray FieldArray() As Variant) ' Declare the two local variables. Dim I As Integer Dim currentVal As Date' Set the variable currentVal equal to the array of values. currentVal = FieldArray(0) ' Cycle through each value from the row to find the largest.
[Code]....
This is working well (I think)
I then want to find the latest date for the 2 records i.e. the Max value for the Achdate.
Query: SELECT [Candidate AC Dates].Candidate AS Expr1, [Candidate AC Dates].Unit AS Expr2, Max([Candidate AC Dates].Achdate) AS MaxOfAchdate FROM [Candidate AC Dates] GROUP BY [Candidate AC Dates].Candidate, [Candidate AC Dates].Unit ORDER BY [Candidate AC Dates].Candidate, [Candidate AC Dates].Unit, Max([Candidate AC Dates].Achdate) DESC;
But this is returning
Candidate Unit MaxOfAchdate TH1 1025/05/2015
I expect it to return
Candidate UnitMaxOfAchdate TH1 10 07/06/2015
It looks to me like MAX is considering only the day value rather than the whole date. I suspect this is because it is considering the results of the function in the first query as a short text rather than a date field. (I've tried to force this through declaring the variables as dates but don't know where else to force this. (I am UK based hence the DD/MM/YYYY format)
My work has a sql server db, and all the tables in our Access db is linked to them for reporting reasons for the secretaries. Why all the rows for one table are not showing up in Access?
I go into SQL Server Management Studio and I can see all the rows, then when I try to go into Access 2010, I cannot see all the rows. I have already updated the table in Access through the Linked Table Manager. However, nothing changes.
I have 10 tables, 30+ fields on each table (every table has the same 'account number' field). I only need from 5 - 20 fields from each table. How do I get the certain fields from each table and put them in a table, query or report?
i'm using Access 2010 and i created a main form , from which i want to let users to open new table with fixed fileds and rows, also the name of the table will be as per user choice. so if a user want to open new table from the main menu, he will have the choice to press on a command button and a dialoge box will ask to name the table. the new table will then be open with the same fileds and rows names( name only) . the new table will be edited using a form.
I understand right off the bat if you're reaction is "don't duplicate data!!" -- mine would be too (don't fret, I know my normalization).
I've linked a table in my db to my Global Address Book in Outlook 2007 and, upon entering an employee number as a new record, would like to verify that the number entered is listed in the GAL and then pull in the associated name and location info.
The key is that I don't want this info to rely on the GAL going forward. For example, if an employee leaves or is no longer listed in the GAL, I don't want to lose the employee info (past data is needed for audit purposes). Note: I will be creating a report later to show if there are discrepancies between the GAL and my table, but that's another story...
So, what would be the best auto-fill options in Access 2007?
How to add my own rows to a query in MsAccess ? I would like to do something like this: Select Name from Table 1 where age > 75 Union Select "Joe" from Dual; Expected Result: If Joe is not found in Table 1 ,then it would be added to the result of the query.How can I do it in MsAccess as it does not use " DUAL" table.
I have created an expense database but I now want to try to add fields to the main form which will allow the users to select their car engine capacity and the price they paid per litre to establish how much VAT can be reclaimed. A small extract from the table from customs & excise is set out as below (although the table headers have moved a bit). There are 5 engine capacity headers and numerous pence per litre rows.
Pence per litreUp to 1000cc1001 to 1500cc 75.08.5259.653 75.28.5509.682 75.58.5759.710 75.78.6009.738 75.98.6259.767 76.19.6509.795
So if someone had a car with the engine size between 1001 to 1500cc and had paid 75.7p per litre for their fuel we could reclaim VAT @ 9.738p per mile.
Is there anyway I can get access to look up this information for me?
