I want to take this data and create six new columns (operator1; operator2; operator3; operator4; operator5; operator6) for each permit number so that each unique operator name will be housed in each of those six columns, with the rows being defined by the permit number. So, I want to produce something that looks like this...
Permit
operator1
operator2
[code]....
At present, there are as many as 6 operators per permit, but the number of operators per permit varies from 1 to 6. I included an example that has 5 operators, to show that I want the sixth column to be null in this instance. So if a permit had only one operator, only the operator1 field would take on a non-null value in the new table/query.I know how to do this with 2 operators per permit by using min/max or first/last functions in a query, but I don't know how to deal with more than two operators per permit.
I have a query which is used to pull data from 2 tables.
1. Only show the records that have 2 or more same Pat#, FName and LName. If it is a single record I would like to hide it.
2. Need to see the different dates amd know the difference.
Currently Query Looks like this.
Pat# FName LName Reason Status Date 00001 John Doe 1 1 11/13/2014 00002 Sally Doe 2 1 11/25/2014 00003 Bill Bates 1 1 11/04/2014 00003 Bill Bates 2 1 11/07/2014 00004 Jenny West 1 1 04/03/2014 00004 Jenny West 2 1 04/10/2014
The Signup reason number 1 represents the signup date and number 2 represents the time they left. SignupStatus number 1 represents a group.
So I am hoping my end goal it would look something like this.
Pat# FName LName Date 1 Date 2 Difference 00003 Bill Bates 11/04/2014 11/07/2014 3 Days 00004 Jenny West 04/03/2014 04/10/2014 7 days
I am trying to avoid using VBA or SQL. I do not know where to input it.
I tried to use the “Find Duplicates” query wizard but this did not work because it finds duplicates like last name first name, but it does not separate the dates.
I am trying to create an auto-sequential order number
Example : AB000112 - AB is standard prefix, ''0001'' is the first invoice number, ''12'' is the year 2012.
so the next order number should read AB000212 and the next AB000312
Out of this, I have to issue several debit notes relating to the same order number when the payments are due from customers over a period of time, this will be
I have a rental database and I print several contacts for leases etc. what I want to do is have a unique reference number or something inserted to the report every time that it's printed. What I am trying to achieve is to keep track of which tenant corresponds to the report (Lease) printed by using reference number.
There is a seperate form which holds the tenants details and I would like to have a field on that form which would show the same reference number as the report so I can track which report was printed for who.
I have an excel sheet, sent to me by someone else. It contains a column of unique information (site IDs), followed by three columns which each contain either a unique value or a blank, something like this:
The ID represents a particular business with which we interact, whilst the codes represent particular types of contract that we have with them.
This is then followed by a lot of columns containing information about each entry, name, address, post code, contract data etc.
I need to relate this to the employees at those businesses, whose data I have recorded in a separate spreadsheet, so that addresses and such are also related to people, not just the business unit.
This spreadsheet records each person by employee ID (a unique value for each entry) and Code, without taking account of whether it is a code of type 1, 2 or 3. Like this:
I figure the best way to create a relationship between these tables once imported into access is via a third table containing a column with all the Codes in a single field and a second field with the sID related to each code.
:D I am trying to create a CrossTab query from a table of expenses:
tblExpenses: PropertyID ExpenseType Amount
There are many kinds of Expense Types. I want to use ExpenseType as the column header, BUT I want there to be 3 categories "Rent Expense," "Taxes," and "Other."
HOW do I group all expense types <> "Rent Expense" and "Taxes" in the third column?
I can get a comprehensive query with many columns, or set criteria to get just "Rent Expense" and "Taxes" columns, but I cannot get the 3 columns.
I was wondering if anyone had code to split a field by semicolums, to create multiple records.The field has the first line of address for each property with a particular postcode. For instance
field name: PRMF Craven House; Ample Mansion; WHSmith; Bulevard Terrace.
I want to be able to split fields like the one above with these properties by the semicolums so they become seperate records, but share the same other fields e.g. postcode, city...
still not sure how to split one field therby creating multiple records from it. For instance: split field: PRMF Craven House; Ample Mansion; WHSmith; Bulevard Terrace.
to
PRMF Craven House Ample Mansion WHSmith Bulevard Terrace.
I have made it a little clearer on the attached word doc.
The plan is to prompt the user to enter a postcode, then based on postcode an sql statement splits the fields of the field metioned with the criteria on a query. Any help very much appreciated, Thanks in advance.
I have a Comments field on a form. I would like to set it up so that multiple comments can be added and logged at different times by different users. I have attached a picture of what I am looking to do.
Hello, i have a database that simply holds all the information on one table at the moment and what im trying to figure out is how to create a form that i can enter data onto that searches all the different fields at once, instead of one search for one thing and another for another. Some of the fileds are Headset number, gasket number, make, model, engine cc, etc.. so what i want to be able to do is have a form with a text box for each of these fields that then searches the table to narrow down the search results. at the moment i have a search for each, say for one i enter the headset number, it searches and pulls up all records with that headset number, then i have a search for engine cc, but what happens is it searches all records again for the engine cc not following on from the search for headset number so i end up back with a load of records with diferent headset numbers but with the searched engine cc. Basically the search form needs to take data entered into each text bow for each field then the combined results from each, narrowing down the results. can anyone help? in laymans terms!! Thanks Paul:
I have a columns that do not incur a count value within the time period of my query, yet i still want them to be displayed with a 0 or no value in the query view, how do i do this?
Record Primary Key: ID_Wells..The TxtFedStCo has Fed, St, Co, ... in one field - and a Dt_Apd_Sub (date submitted) .There can be zero or many dates submitted for each Fed or State.
Objective: If A Fed has (1 or many) date submitted AND A St has (1 or many) date submitted Condition is TRUE
How do I get to the next step? The reason for doing this in SQL is to prototype in Access, then move this over to TSQL later.
I've made a crosstab query and would like to use it to create a subreport. In the column headers I have names of courses. Courses can be added or removed. How can I make a crosstab report with dynamic columns?
PHP Code:
TRANSFORM Count(tblCourses.CourseName) AS CountOfCourseName SELECT tblNmscStaff.NmscStaffFirstName, tblNmscStaff.NmscStaffLastName, tblNmscStaff.PtOrFtNtl, tblNmscStaff.Ntl, tblNmscStaff.NmscID FROM tblNmscStaff LEFT JOIN (tblCourses RIGHT JOIN [tblNmscStaff/CoursesPointer] ON tblCourses.CourseID = [tblNmscStaff/CoursesPointer].CourseID) ON tblNmscStaff.NmscStaffID = [tblNmscStaff/CoursesPointer].NmscStaffID GROUP BY tblNmscStaff.NmscStaffFirstName, tblNmscStaff.NmscStaffLastName, tblNmscStaff.PtOrFtNtl, tblNmscStaff.Ntl, tblNmscStaff.NmscID PIVOT tblCourses.CourseName;
I am making a classic sales over time crosstab query.
Rows: Customers Columns: Sale months
Sales date is defined by the ETD of the order.
However, with the simple Format([ETD],"yyyy-mm") I get regular months, but I need to adjust the months to be between the 21st and 20th rather than 1st to 31st(30th).
August would be 7/21/2013 to 8/20/2013 September 8/21/2013 to 9/20/2013
I then created a 2nd select query with inner joins to join both crosstab queries on similar fields (activity & projectname).
SELECT QUERY 2: projectname, activityname, employeename (totalhoursworked as value), employeename (calculatedcost as value)
It gives me this:
However, I want it like this:
Those employeename... refers to more employees being added after a period of time. Hence I want to know if I could use vba to generate a report every time a button is pressed on a form? I know how to link the form to the query.
I'm trying to run a query which fetches only the last 2 years of data for a given region from a table with several years worth of data (there are year, region, sector and rank columns among others). The region is passed into the query from a combobox from Form1.
The first problem was that some regions have up-to-date data and some not so much e.g. for Europe the "last 2 years" mean 2012,2013 for Asia its 2011,2012. In order to deal with this I've created a crosstab query which works well except for one thing - because the columns are dynamic (dependant on the region) the column headings change as well.
And here comes my question, how can i fix the column names to be e.g."Current Year" and "Prior Year" independent of the query fetching 2012,2013 or 2009,2010? I've tied different things with PIVOT... IN ... but with no luck.
Here's the sql for the query:
Code:
PARAMETERS [Forms]![Form1]![cmbRegion] Text ( 255 ); TRANSFORM min(DataTable.Rank) SELECT DataTable.Region, DataTable.RegionalSector, FROM DataTable WHERE (((DataTable.Region)=Forms!Form1!cmbRegion))
I need to be able to enter a number of orders that would all be the same, but they will have different order numbers, in sequence.
I'd like to enter all the information in a form, then have "First Order Number" and "How Many?" fields, and have the system grab the detail, apply it to the order number entered, create a record in the table, then reapply the detail to the next sequential number, repeating the process until it has created the number of orders shown in the "How Many?" field.
I am at a total loss here, and any help would be GREATLY APPRECIATED!!
I have a MS ACCESS 2010 database with a data table which i am trying to create a query from. I have 6 columns of data( one with an ID Field and 5 Name Fields). Below i have made examples of how it first appears as a simple query and the second will show you what i would like it to look like.
What the simple query looks like: [URL] ...
Second what I want the query to look like: [URL] ....
I am creating a CAD Drawing database / Distribution list. I can only seem to distribute one drawing i.e single field to one person.Is there a way to have multiple distribtion on one field without creating multiple distribution fields?
What I have is a database which contains Drawing Numbers - I need to create a report that shows where the drawing has been distributed to, with it's current revision status and the date is was sent.I am having trouble trying to use multiple revisions and mutiple people in the distribution list with one drawing.
I have a table like the one below. I need to Auto Sequence based on JobIDOne in the field that says sequence in the manner that I've typed. Hand typing is not an option because by table will be updated regularly. All other fields are updated via a form. I need a either a macro or VBA solution that can reconcile this, preferably through the table. Note, I do not want to use a query to create this sequencing or SQL language as I do not know how to write SQL commands.
I know it can be done but I've seen about a thousand ways to do it that I haven't been able to modify for my specific table.
DateOne TimeIn JobIDOne InitialsOne Dates Worked One Sequence
Hi There, first time on here, so hopefully i can get an answer to my problem.
i am trying to split data in a sigle column accross multiple columns. basically i have a table like the one below:
Member DATA A 34 A 65 B 234 B 456 B 22 C 88 X 6 X 34 Y 23 Y 353 Y 25 Y 9 Z 45
so what i want the query to do is to show a list of Unique Members, and then split the data column so it show an entry for each Member accross mulitple columns.
is this possible? i have very limited access knowledge, so a comprehensive answer will be very much appreciated.
I have a query that groups the data by customer. How can I create a "Transaction Number" field where I assign a sequential number to each invoice, starting at 1 with the oldest invoice and increasing the sequential number by 1 with each invoice. I need to create this field in a query without code.
