Reformatting Data
Aug 27, 2007
Hi all,
I am putting this in General because, frankly, I have no idea where it belongs.
I have a table that looks something like this:
Serial...Part.....Batch
1001......1.........A
1001......2.........A
1001......3.........B
1002......1.........A
1002......2.........B
1002......3.........B
1003...
How can set up a form, or a new table, or something so that the data is displayed like this:
............1.........2..........3
1001......A.........A.........B
1002......A.........B.........B
1003...
You see what I am trying to do? I am trying to make the Parts a column header, and kind of remove Serial/Part/Batch titles.
My first instinct was to take it into Excel and make a pivot table out of it, but that doesn't seem to work? The pivot table keeps counting up everything, and I don't want anything counted, I just want all data displayed.
Can anyone help? Thanks in advance.
View Replies
ADVERTISEMENT
May 25, 2006
OK, i'm getting a type mismatch. I've tried searching for that and read a lot of posts, but none hit the nail on the head.
I know where it is. I am trying to link two table by using a field which is a string in one to link to a field which is a number of some kind in the other. I can;t change the formats of these as it's a query i'm running on a database i don't control. I understand I need to convert the format of one of the fields in a query and then link using that, but when i try to reformat the number it doesn't do what i'm expecting after reading the help.
in the help it suggest using this:
Format(expression[, format[, firstdayofweek[, firstweekofyear]]])
So i'm trying to use that to reformat the string field to a number so i can use that query.
Numbers: Use predefined named numeric formats or create user-defined numeric formats: This is where i fail completely!! it just rejects everything i write in my query.
View 2 Replies
View Related
Jan 23, 2013
I am using a template that I downloaded off of MicrosoftOffice.com. I really like the setup but I need to make a few modifications to it, but the modifications I want to make I can not do due to it being a web-based(I think). I am a newbie to access and I am slowly learning about macros, vba codes, formatting, and what not.
View 5 Replies
View Related
May 29, 2007
Hi,
I have an address field currently formatted 1 Streetname, city, post/zip code it is displayed on a form within a multi-line textbox. I'd like to change the commas to a line return so it will display
1 streetname
city
postcode
I was thinking that a small macro might go through each record and change the comma to a line return? But not sure where to start exactly?
many thanks in advance. Harry
View 3 Replies
View Related
Dec 7, 2006
Hi
I have a table with postcodes (1.6M rows) that have had all the spaces removed. I want to match another table to it and ordinarily would have removed the spaces from the postcodes in that but I think because of the vol (2.5M rows) Access is reluctant (it either crashes or only updates a few thousand).
A better way would be to reformat the postcode using some SQL or VBA code. The trouble is the postcodes are different numbers of characters and the space would either need to be after the 3rd when there are 6 characters or after the 4th when there are 7 characters.
eg PO121DL becomes PO12 1DL
PO91UL becomes PO9 1UL
I can concatenate fields and count characters but am unsure as to the best way to approach this in terms of coding and structure? Using Access 2002.
Any thoughts/tips etc would be appreciated thanks
View 2 Replies
View Related
Mar 26, 2008
We have imported an Excel spreadsheet with dates in one column that were imported as numbers such as '39287', which can also be displayed in Excel as July 24, 2007. We concatenated this import to a table where all our dates are stored as 20070724. Now we have both formats in one column, with over 70,000 records in the 20070724 format and a few thousand in the Excel format. How can we convert the fields in the Excel format to the appropriate YYYYMMDD format we use?
Many thanks for your help, we can find a good way to clean this up...
Genevatexan
View 4 Replies
View Related
Jun 19, 2014
I've inherited a database into which the user (not me) is entering essentially the same Date data twice: field 1 contains 6/19/2014 and for field 2 they enter June-14 (the month and year from field 1).
I suspect there should be a way to derive and then reformat the display of field 2 based on the contents of field 1, such that the user only has to enter the first. Is this true? If so, how?
(I know there really is no need for the 2nd field, but existing reports and queries are built around it -- so for right now, I'm just trying to eliminate the duplicate data entry)...
View 7 Replies
View Related
Mar 27, 2008
If my make queries in the data base and the source data base is another .mdb and the table names in the other .mdb which would be used for the queries are the same as those in the data base where the queries would be made......does anyone see any problems with that in the area of corruption or similar.
The queries made would be indentical to their counterparts in the data base where they are made and would serve the same purpose.
It would be a toggle type of thing whereby the recordsources for the forms in question would be changed.
For what I want to do it works perfectly but I am not sure if there would be problems that would only surface with longer term use and varied conditions as opposed to some short term testing.
View 14 Replies
View Related
Apr 16, 2014
I have data from a survey with qualitative responses. For a single qualitative question, I moved the ID & responses into a new table and categorized the response according to a bucket/theme, where each column is a new bucket. I now have 10 columns. Each response is represented in 1 or more columns. I used an excel formula to copy the response data into the column itself.
Example:
A1 // B1// C1 // D1// E1//... L1
ID // Response // Cats // Dogs // Elephants //.... Column 10
1 // I like cats // I like cats //(null)//(null)// ... (null)//
2 // I like cats and dogs // I like cats and dogs // I like cats and dogs //(null)//..//
3 // etc.
However, now I'm realizing that Access always wants to show data for all records, or at most I can limit using a WHERE clause in my query.I want to use Access to generate this report:
1. Section 1: Show all responses from the Cats bucket where there is data
2. Section 2: Show all responses from the Dogs bucket where there is data
3. and so on
I know how to do summary values, and I know how to do filtering that apply across the whole report, but this seems like more advanced filtering, where I want to see selective details differently for each field.
View 3 Replies
View Related
Mar 9, 2006
First I would like to give thanks to all the knowledgeable folks here who have helped me with my DB to date. It is working and every one is very happy and I have learned a lot.
So now I would like to add some more functionality to this existing project.
My DB is for data input of customers for a drawing. It has the following fields: Id, account number, first name, last name, date/time, score1, score2.
I t is taking a great deal of time for the users to enter in hundreds of entries a day. Most of the entries are customers who are already in the DB. I would like to get the fields to auto fill the data for existing customers say after the account number is entered. So after you put in the account the name and any other pertinent data would shows up saving users from typing it in again.
The first problem I am having is that this is still a data entry form and I can’t figure out how to be able to see the account information and still add new data to the record? The new data is a daily score they get.
Second I haven’t figured out how to call up the customers information from just the account field.
I’ve googled this and haven’t found anything terribly helpful.
View 2 Replies
View Related
Oct 24, 2007
i would like in a form for a combo box to be able to select an item from a table and input relating information automatically into other boxes in the form..
I have 3 tables: Table 1 has product code and product description.
Table 2 has invoice number company details, address etc.
Table 3 has product code and product description qty and invoice number..
Table 3 relates to table 2 by the invoice number and table 3 product code looks up the product codes available in table 1 and also table 3 looks up the list of products descriptions in table 1 using the combo wizard.
This means the wrong code can be put with wrong description.
What i would like to know is how i select a product description and the product code in the form fills out automatically?? i hope this makes sense please helppppp!!
View 5 Replies
View Related
Mar 5, 2015
I an trying to create a data entry form (IndividualsEntryFm) to input data for fields such as (First Name),(Birthdate) etc., these to be saved to the (IndividualsTbl)
I also have another table (NamesTbl) which has family names etc. The two tables are linked by a (MainID) field. I want a combo box on the individualsEntryFm so that I can select the family name. Then I wish the empty fields for the IndividualsTbl to be available to enter data.When I press the save button I then want this data saved, together with the MainID from the combo box to the IndividualsTbl.
I have set the IndividualsTbl with a (PersonID) field as an auto number each individual therefore has a unique PersonID but may well share the MainID. I'm trying to link many people to the same address.
View 1 Replies
View Related
Feb 9, 2015
I want to use the same form in datasheet mode for data entry and retrieval. When retrieving, all controls are disabled and locked. I am trying to enable and unlock them for modifying but that isn't working.
<code>
DoCmd.OpenForm "PO_Practice Data", , , , acFormEdit, acHidden
Forms![PO_Practice Data]!PO_Name.Enabled = True
Forms![PO_Practice Data]!PO_Name.Locked = False
Forms![PO_Practice Data]!Practice_Name.Enabled = True
Forms![PO_Practice Data]!Practice_Name.Locked = False
[code]...
View 3 Replies
View Related
Dec 30, 2014
I would like to filter data from a table using a query (from an data input form). The objective is to output all results if input form field is empty and to output results higher or equal to the type in the field if field is not Null. The query code is as follows:
IIf(IsNull([Forms]![Form2]![MaxDiffInput]);[Maximum operational pressure (bar)];[Maximum operational pressure (bar)]>=[Forms]![Form2]![MaxDiffInput])
However, is not providing any result when the input field (MaxDiffInput) as a value.
View 5 Replies
View Related
Aug 5, 2014
I am using Excel/VBA as a frontend and Access backend. The sheet2 stores the queue name and Queue number. We have to update the sheet1 from column L to column O by looking for the values from the Access table for the date selected from the comboboxes. Now In sheet 2 , it says Queue number and in actual in access table it is the combination of Type & Type1 & Type2. So we have to look for Type & Type1 & Type2 in the table and find out total Batches ,Total Envelopes,Total documents and total pages and then store the values in the ExcelSheet1 from column L to column O.
The following formulas will be used in the select statment:
Total Batches = count(BatchNo) for date selected
Total Envelopes=sum(Envelopes) for date selected
Total Documents=sum(Cases) for date selected
Total Pages=sum(Pages) for date selected
View 14 Replies
View Related
Aug 10, 2012
I want a Text Box Query on my form to display the Status, Workshop, Time, Enrolled and Limit. The problem is these values come from two different tables and the Enrolled value comes from a single field that contains the different workshops.
What I mean is: In Table[Attendees] a row contains a customer's Number, First Name, Last Name, Workshop and Phone Number. The workshops vary for each customer so one row on the table could have John Doe attending Cover Letter Writing and the next row could have John Smith attending Resume Writing. What I want is to be able to count the different workshops within the Field[Workshop] and total them and then display the total in a Text Box Query. I have a Text Box Query set up displaying Status, Workshop, Time and Limit as these values all come from Table[Workshops].
So basically I need to Query to also display a result that is the Total for each workshop from Table[Attendees] and display the total for each workshop in a Query with data from Table[Workshops].
Here is a link to an Example Database [URL] ....
I'm trying to avoid putting things on different reports and the like because the people using this are basically computer illiterate and if they have to click a button (no matter how well labeled) they won't do it and the information might as well not exist.
And if there's a better way to do it, I'm all ears. The only thing is, I have to update these workshops month by month. Since they are dynamic, I want to avoid creating separate tables for each workshop.
View 14 Replies
View Related
Nov 11, 2013
i have a main form named(EMP) i have a subform named(SEMP)with EMPID i have an another form Named(SDetail) with EMPID i want to open form Sdetail with filter records for data select in subform (SEMP) ,EMPID field Subform SEMP in as datasheet view. i can open sdetail for selected records only
View 14 Replies
View Related
Apr 15, 2013
what I want to do is make a button to search range of columns in data table with data type Yes/no and display the results if the value is yes
View 9 Replies
View Related
Dec 18, 2014
I am trying to save specific data from a data entry form to a specific depending on what the user selects from a dropdown/combo box.
So if the user selects "test company from the combo box, that data (from that form) will save to the Test company data table.
View 5 Replies
View Related
Aug 1, 2007
I have a database that I import data from an excel spreadsheet into multiple times daily. The table that this data is imported into has several key fields that if the data already exisits in the table, and I attempt to import data that is the same except for one or more of the key fields is different. At this time the database it creates a different record. I am trying to get the database to overwrite the data in the database.
View 1 Replies
View Related
Feb 18, 2015
My aim is to have my forms open to a new record, which I have done, but if my users need to then update or edit data in previous entries, they can click a button that allows this.
My thoughts were to add a button, then put in code so that the necessary properties changed the form from displaying a new record or records entered since opening it, to showing all records in the associated table....
As an example there is a table for purchase orders. When clicking on this from the main menu form, it opens up the purchase order form to create a brand new PO. At times though, we will need to revisit an order to attach a copy of an invoice, or update the cost of whatever was purchased.
View 5 Replies
View Related
Jan 5, 2013
My question is this: I have a table where I'm entering employees' hours worked. Basically, it's something like this:
ID WorkerNumberDateworkedTimeStartTimeEnded
121/2/201310:00:00 AM3:00:00 PM
221/3/20132:00:00 AM11:00:00 AM
321/4/201312:15:00 AM11:30:00 AM
421/5/201310:25:00 PM11:00:00 AM
531/2/201311:00:00 AM3:30:00 PM
631/3/201312:00:00 PM10:00:00 PM
731/10/20137:00:00 AM4:00:00 PM
I have a query that (easily) determines how many hours an employee has worked on any given day. What I can't figure out at all, is how to write a query that can figure out how much time an employee had off in between shifts.
Thus far I'm able to run a query that separates this main table into individual workers by their id numbers, but can't figure out how to determine time off between shifts - as the last hour worked one day, and the first hour worked the next day are on two different lines (they are two different table entries).
View 4 Replies
View Related
Oct 1, 2013
Background I have a query (Q1) that retrives data from a table (Table 1). One of the fields in Table (F1) contains both text and numeric data (ie: 24 eggs). I want to separate these values in Q1.
Questions
How can i in Q1 retrive only numeric data from F1 and display that data i a field?
How can i in Q1 retrive only text from F1 and display that data i a field?
View 3 Replies
View Related
Jun 2, 2014
thos is f.i. a json data sting :
{"vip_kaarten":"0","reg_kaarten":"0","extra_vip_ka arten":"0","bedrag_extra_vip_kaarten":"0.00","extr a_reg_kaarten":"0","bedrag_extra_reg_kaarten":"0.0 0","vrjr_kaarten":"2","extra_vrjr_kaarten":"0","be drag_extra_vrjr_kaarten":"0.00","website_link_spon sorpagina":"1","website_banner":"","social_extra_m ededelingen":""}
How can i convert this to readle data?First string is the field name, second the value.
View 4 Replies
View Related
May 2, 2014
I am trying to use SQL to run queries in our access database in order to (hopefully) speed things up. I'm trying to create code that basically takes data from one table and inserts it into another whilst doing calculations on the data.
However I can't get past this:
Code:
Private Sub Test_Click()
Dim strSQL As String
strSQL = "CREATE TABLE [TempRedAmberGreen]" & _
"AS (SELECT " & _
"[ID_CHK] String," & _
"[Red] String," & _
"[Amber] String," & _
"[Green] String)" & _
"FROM [035 - Meter Point HH Data];"
DoCmd.RunSQL strSQL
End Sub
It keeps saying "Run-time error '3292': Syntax error in field definition.
View 4 Replies
View Related
Sep 28, 2014
My db has multiple linked tables. As time passes and work on a project continues data needs to be added to a separate table that is linked to the first table to be populated.
i.e., Initial design data is entered first, when drilling is complete more data is added to a linked table, when drilling is surveyed yet more data is added to the second table.
What I want to be able to do is to query for a particular set of existing design data, display this on a form and on the same line enable data entry to other fields from other tables which are linked to the existing design data.
View 7 Replies
View Related
