Access Reports: Adding Lines To Separate Rows And Columns Like Excel
Nov 4, 2004
Ive been converting .xls files to Access database files. I would like to use ACCESS to develop the Reports but the client wants the Reports to look like those in EXCEL..eg. where you have lines between rows and columns. If I could give my Access Reports the same look and feel, I could wean these guys off of Excel and into the wonderful world of relational models.
Does anyone have sqlcode or tricks I might use to create the Excel 'look alike' report in Access?
I am just starting out learning how to connect Access to Excel. From an Access form, I need to connect to an Excel spreadsheet, and loop through every row/column to look for data that matches a database field data, then populates the associated cell data. I don't want to import the spreadsheet since the user who created it is using it manually, and I am attempting to automate the task. This is probably the most elementary code, but I would like to start out with the best way to do this. What I am finding is code that appears to be much more complicated than I need, for more complicated situations.
I have a database with a form that has 3 fields in a subform. I need to paste there data from multi rows. Now I have to do this column by column and paying attention that I select them correctly. Is there a way to directly insert the data by selecting only 1 field and have the data directly there as it would in excel?
Now we use excel as a "between" road to paste it there and then copy it from excel and paste it into access.
I have a database with all the hours employees have logged stored in the database. Our payroll company wants an excel spreadsheet that has very specific info in particular columns and fields on the excel spreadsheet, so I'm trying to design a query which will put the correct info in the correct fields per their system.
The challenge is, I have currently a query with Employee ID, Overtime Hours, and Regular Hours as separate columns.
I need to translate this to a query with a single column for hours and a separate column that designates those hours as OT or Reg, with two rows for those employees who have both types.
I don't know how to create a query or a formula in a query to break out each employee row into multiple rows with different data in the hours column. It seems like there's something pretty straightforward that I've done in a similar vein but it doesn't seem to work - I can do the opposite and combine those hours by using the SUM function in a query, but I can't seem to break it out this way.
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)
Hey, does anyone know how to set up a formula that will allow me to turn either one column on, or another, but not both at the same time for data entry eg. one column for dollar figures and another for sterling currencies?
I'm trying to make a database to track inventory or several items. Basically, I have four tables:
1) RawMaterialList - includes a list of all raw materials. 2) PartList - includes a list of all finished product using said raw materials. 3) RawMaterialRecieving - contains details from each packing slip of incoming raw materials. 4) ShipmentRecord - contains details of daily shipments.
Each of these tables is fed by a form of the same name. I should note at this point that I basically taught myself how to use Access and I imagine I'm in the dark about quite a few things it can do. I've made several databases over the last few years, but I'm stumped at this point.
Here's my problem. In the form RawMaterialReceiving, I have several fields aside from basic information:
1) Item - a list of of raw materials from table RawMaterialList 2) Description - also dependent on info entered into table RawMaterialList 3) Quantity
My problem is I want to add up the quantities of each raw material and I'm not sure how to go about that. Lets say on May 13, I received 15pcs of Part A and 20pcs of Part B. I enter this information as Item1 and Item2 respectively. On May 14, I received 30pcs of Part B. I enter this information under Item1. Now I want to add up all of Part B (50 pcs). But Part B has one value listed in the field Quantity1 and one value listed in the field Quantity2.
i am having a table in access from where specific columns need to get exported in excel.these things i need to do:
1) Excel where the data is exported, some cells have formulas which is needed by customers 2) The column names are different 3) and at the end of all the data there need to be sum of specific columns how do i do it?
I don't know if it is the right place to post this question, but I know many people here are good at both Access and Excel. The question is:
In an excel sheet, A1: Smith, John A2: 111 Pine St. A3: San Diego, CA A4: (555) 128-549 A5: Jones, Sue A6: 222 Oak Ln. A7: New York, NY A8: (555) 238-1845 A9: Anderson, Tom A10: 333 Cherry Ave. A11: Chicago, IL A12: (555) 581-4914
3. Fill this formula across to column F, and then down to row 3.
4.Note that the data is now displayed in cells C1 through F3 as follows: Smith, John 111 Pine St. San Diego, CA (555) 128-549 Jones, Sue 222 Oak Ln. New York, NY (555) 238-1845 Anderson, Tom 333 Cherry Ave. Chicago, IL (555) 581-4914
This is exactly what I want. And I can do step 3 manually to copy that formula to each cell. But, My question is in step 3. If I have thousands of lines, how can I fill that formula to thousands cells? from C1-C1000, F1-F1000.
How to keep Access from including the detail line items in my report total?
Below is a quick image of part of the report.
The report is compiled from two tables. The first table has detail information on each line, in four columns:
State Name Amount Quantity (Not used)
AL Store 1 313.94 60 AL Store 2 12.59 1 AZ Store 3 576.45 90 CO Store 4 172.22 24 CO Store 5 502.25 88
The 2nd table is a summary by state with three columns:
State Amount Quantity (Not used) AL 326.53 61 AZ 576.45 90 CO 674.47 112
I'm trying to make the report group by state, then name, amount and a subtotal for each state. This all works fine in the report.
I want to also add the amount field from the 2nd table (State Subtotals) to the report just for reference. I've managed to make this work as well. (See State Total on image)
The problem that I'm having is that when I try to do a final total of the State Total column in the report footer, it seems to add the (State Subtotals) amount field on every detail line (See red numbers in picture. they are not actually on the report, but Access thinks they are and includes them in the final total).
So far I've tried different joins, hiding various fields, sub reports, ect. I just can't figure out how to stop Access from calculating the detail lines.
I have a WO Table and a related WO Procedures Table. I would like to add a specific # of blank lines/records between each record but that # will vary. So the report will show the WO Procedure Desc then a specified # of blank lines below it and then the next WO Procedures Desc then a specified # of blank lines below it etc.
I thought to add a field to the Proc tbl called #ofLines and whatever number was entered would be the # of blank lines to appear below the record on the report.
Report 1. is attendance from January to June Report 2. is from June to December Report 3. is from January to December
The are all run from the same query with different Between Dates.
The scenario is that Paul attended the support group from Jan to June so in the Jan to June report he is counted once. The query removes duplicate values
Paul attended the same support group from June to December so in the June to December report he is counted once. The query removes duplicate values.
This shows the Paul attended he support group from Jan to December so in the June to December report he is counted once. The query removes duplicate values, if you removed the duplicates from this query he would be counted twice.
Not all people attend the same group for 12 months but if they do the are a duplicate and Paul is a duplicate. That is ok because we need to know now many duplicates there are for the year.
If I print all 3 reports with the duplicates remove query and you looked at the totals it would show (Example) 30 attendees in the 1st report and 30 attendees in the 2nd report but in the 3rd report it would show 59 not 60 which is the total of reports 1 & 2. we need to report these figures as 1st 6 months with 30 and the second 6 months with 30 but the whole year would be with 59 and 1 duplicate.
The way it is now I have to run all 3 report and do the math by hand this way.
Is there a way on the to do the math with code calculations on the full year report with the figures from the 2, 6 month reports to automatically show total attendance for the year 59 with 1 duplicate?
I have been importing excel spreadsheets succesfully for a long time now and this new problem I have never seen before. I don't know how to deal with it. I have had a look on here but could find nothing similar, I have not really had a any luck searching google, mainly because I am at wits end to find a suitable search term.
Here's what's happening: When i upload the sheet as is, it works fine, bar a "data conversion error" for one column which contains mainly numbers but a few cells of letters.
as before, I have inserted a line at the top of the spreadsheet (below the column headings), and put a 1 in for each numeric column and a letter for each alphanumeric and text based column.
Now when i import to access, I get no conversion error, but access has inlcuded about 20'000 blank lines between the headings and the actual data.
I face a multiple line problem when i try to import data from Excel to Access db. Is it possible to keep multiple lines of a cell after transferring to Access. Since Access see any ceel of Excel as a Field, it takes all the lines as a line in the same row.
In enclosed, you can see a sample Access document. I put a point(.) between every lines in the same field manually. Is it possible to split up those data from these points and make a new line in the same field by VBA? For example,
I have two collums, currentowed and currentpaid. i want to add another field to my report showing the percent of currentpaid to currentowed but I can't seem to be able to make it work. I tried:
=sum([currentpaid]/[currentowed]) and it doesn't work.
I have a specific form, paper version, that I'd like to reproduce in an Access report. This form, which has 34 blank lines in the paper version, is used when people need to borrow some Tools or Equipment on a short period of time.
My report is made up in Access, but, if a person signs for, lets say, three different piece of Equipment, once it is sent to the printer, it prints three lines, which are the equipement signed for and it leave a big empty space before it reach the page footer.
What I need is something that will fill that empty space with blank lines. I found the following on the Net which meets my needs up to a point. What I mean is once it print the last line, it keeps on printing the last line until it meets the total count of 34 lines.
Here what I found on the Net: (note that I have a =count(*) into the Group Header)
Option Compare Database 'Use database order for string comparisons Option Explicit Global TotCount As Integer ' Call the SetCount() function from the group header section's ' OnPrint property using the syntax: =SetCount(Report)
[Code] ....
I submitted an attach file (blank_lines.gif) to show you what I end up with.
I decided to link Excel files and all is running fine except when I run a query on the file I get about 1,041 blank rows before my data is displayed in the query's datasheet view or any report based upon this query... the data seems good but why the leading blank rows? The data in the linked Excel sheet does not look like this and another Excel file's query looks fine and they seem set up identically!
I was able to use the UNION ALL qry. But, when I have another file (like original2) that does NOT have all the columns listed in the UNION ALL qry, I get a Parameter value box asking for the missing columns when I run the qry.
original 2IDDateGroupChristianJohnny18/6/2013A212528/6/2013B2338/6/2013C2248/6/2013D22
The UNION ALL qry includes all the possible resources ( includes all the possible column fields Christan, Johnny, and Steve).
When I run the UNION ALL qry with the original2 file, An "Enter Parameter Value" box is displayed with the mssing column name "Steve".
Is there a way to Map the original2 table into a working table with all the columns, or use VBA code to construct the UNION ALL qry to only include the existing columns? My data has variable columns and I'm trying to avoid the parameter popups.
I'm using the below code to attempt to export data from Access to an Excel template (Access & Excel 2010). The export works fine with one exception: Only the first ~150-200 records export when I'm expecting to export over 1,000.
Code:
Dim db1 As DAO.Database Dim rs1 As DAO.Recordset Dim mySQL1 As String Dim strSheet1 As String Dim strFolder1 As String
Hi, I am fairly new to doing DBA, and I am having trouble adding a new column to a table that is existing. Is there any simple way to do this or does it have to be done throught code? And if so how. Thanks zorter8
The access file has a lot of rows which do not show on the excel file. The added files in access either have a space before their names, an * before their names, or no name at all. I would attach the new access file but it does not show up on my windows 7 list of files on my computer. It only shows up as a new file on my access program.
