Queries :: Access Split Zeros When Combine Fields Of A Table
Apr 25, 2014
I have a table "MansComps" which includes two fields, the "Index" field which contains a number, e.g "5" and the "Tabletype" field which contains a letter e.g "T". i have created a query based on this table, which combines these two fields and displays the result to a third field which I call "Components".
field: Index
1, 2, 3,....
field: Tabletype
T
field: "Component" has to be like this:
T00001, T00002,...
I have managed to combine the fields in my query using the expression:
Component: [Tabletype] & " " & [Index]
but the results appear without the leading zeros, like T1, T2..I have set my Index content to be a number and as a primary key and the Tabletype as a text. I also tried to change the format of Index to "00000" which works for the Index field separately but not for the Component field.Should I do something in the format of the Component field?
View Replies
ADVERTISEMENT
Apr 22, 2015
I have a product table that includes the name of the product, the quantity ordered, and the unit price. This is a summary table for the whole year showing all of the orders. The Primary Key is [ID] from the [tblOrders] table. I attached a screen shot of my query. I don't know if you can see it. I want to generate a report where each product is listed once, the unit price for that product is listed, the total number of that item that was ordered throughout the year, and finally, the total amount spent for each item during the year.
View 2 Replies
View Related
May 6, 2014
I want to combine six different memo fields into one. I found this code and it works to combine two fields so I edited to add a third and it does not do anything.
Code:
Comments: [QAComments1] & IIf(IsNull([QAComments2]),"","; " & [QAComments2])
This is my edited code to add a third field:
Code:
Comments: [QAComments1] & IIf(IsNull([QAComments2]),"","; " & [QAComments2] & IIf(IsNull([QAComments3]),"","; " & [QAComments3]))
What am I missing to add the other fields?
View 3 Replies
View Related
May 13, 2014
I get tasked to use access very infrequently but now I have been asked to create a database. I am struggling with combining 2 tables. I have different data on each table however I do have a unique Identifier. So on table 1 I have Bud, his height, weight, etc. On table 2 I have Bud his home address, phone #, etc. I am using name "Bud" in this case as my unique identifier. I want to create a query that gives me Bud, his height, phone # etc. I want my query to pull in all records. Bud may only have info on table 1 and Budette could be on table 2 but not on table 1. I would like my query to include all the unique identifiers and as much info as I have in the tables.
View 7 Replies
View Related
Jan 30, 2008
Hello,
In access 2003 i have an external Oracle table named EXT-COM_EXTRA_DATA_FIELDS
in this table I have the following fields: COM_DATA_KEY, COM_DATA_FIELD_NO, COM_DATA_VALUE
From this I have created a query named: LOOKUP-BARCODES-10 this looks at the above table by filtering the
the records on the field: COM_DATA_FIELD_NO where this =10.
Also I have created a second query named: LOOKUP-BARCODES-20 this looks at the above table by filtering the records on the
field: COM_DATA_FIELD_NO where this =20
Below is sample data in the table:
COM_DATA_KEY; COM_DATA_FIELD_NO; COM_DATA_VALUE
009A/s 10 03010303165001
009A/s 20 04350309115002
010B/P 10 05150909116001
010B/P 20 06150909116002
011C/X 10 07281809217003
011C/X 20 N/A
The problem is i would like to combine the numbers in the COM_DATA_VALUE field for the same product (ie the same COM_DATA_KEY
record) on the same rows without any duplicate records as shown below:
COM_DATA_KEY; COM_DATA_VALUE; COM_DATA_VALUE (2)
009A/s 03010303165001 04350309115002
010B/P 05150909116001 06150909116002
011C/X 07281809217003 N/A
As you can see from above some records only have data in one COM_DATA_VALUE (like 011C/x) where COM_DATA_FIELD_NO = 10 but no
data for the same product where the COM_DATA_VALUE = 20.
Im thinking maybe i can create a new join query that looks at the above two query’s and combines the values in the COM_DATA_VALUE for
the same product onto the same record row but im not sure how to go about this without creating duplicates & its been some time since ive done joined queries. Any help would be great?
ive attached a cut down version of the database with the table imported from oracle rather than being linked to oracle if this helps.
Thanks
Kevin
View 2 Replies
View Related
Feb 14, 2013
I have a table called Packages, in this table I have multiple fields with just short dates example of fields (ID), (Home_1), (Home_2), (Home_3) and (Home_4). What I was wondering is there a way to comine all of the Home fields into one for the purpose of counting the number of dates for all the records. Total number of dates of all the Home fields? I am using Access 2010
View 1 Replies
View Related
Jan 22, 2014
How can I combine several records in a table into one record?
Suppose that I have a table like Table1 in the attached image.
Then I want to combine all records with the same value for Key1 in one record.
The result is shown in Table2 in the attached image.
I would prefer to do it using SQL only, but I guess that this is not possible. Is it possible?
Alternatively I could accept to turn to VBA that could do it. Any good links about this?
View 3 Replies
View Related
Jun 20, 2006
I have a database where the primary key is a field for pass numbers. Many of the pass numbers begin with zeros (example: 0023456). I changed the table property for the pass numbers to text so the zeros would be recognized. However, I have a form based on a query to search this pass number field. How can I get the query to recognize the pass numbers that begin with zeros. When I put in any other number above zero, the pass number satisfies the query and the employee information pops up. Aside from AllowZeroLength and trying to format the text field, I cannot get the query to recognize the pass numbers that begin with zero. Please help:eek:
View 3 Replies
View Related
Oct 27, 2014
My boss made a satisfaction database that tracks satisfaction in 2 ways, so we have:
Table
ID
SatisfactionAType
SatisfactionAScore
SatisfactionBType
SatisfactionBScore
I'd like to make a query that would split these into two lines, one for A, one for B and then export it to excel.
View 4 Replies
View Related
Dec 19, 2014
I have a table with the folowing structure:
ID(integer)Value1(Double)Value2(Double)
I need to split this table in 4 equals parts:
- count of id must be equal or close to equal(ex: 467 split in 3 x 117 and 116) for each part
- the cumulated Value1 and Value2 must be equal or close to equal for each part
View 11 Replies
View Related
Apr 18, 2014
I've created a report which shows zeros in various columns where there aren't any values to report. I think the report would look better and be easier to read without these zeros. I would like to suppress them and display blanks instead. I'm not sure how to do this.
View 1 Replies
View Related
Apr 24, 2013
I have calculated fields on my tables and used zero values as default to show totals. But when I view my report, all the zeros appear. Don't want those zeros to appear on the report.
View 1 Replies
View Related
Aug 13, 2013
In the following simplified query, in some months(MonthOf) there were no warranties so I have a null field for AcceptedWarranties:
SELECT qryWarranty.MonthOf, qryWarranty.AcceptedWarranties
FROM qryWarranty;
In order to make my Warranty Trends graph work I need 0s. Other postings show the following statement should get the results I need:
SELECT ISNULL(AcceptedWarranties, 0 ) FROM qryWarranty
But no matter how I try to work this into the original code, the compiler finds reason to reject it.
View 2 Replies
View Related
Jul 7, 2006
Hi all,
I have been given the task of cleaning up an old db. One thing I'd like to do is combine 3 text fields into 1 new field.
In the old db there was a form that included 3 memo text boxes, when the first one was full the user then started using the second and then the third. The relating Table fields are message, overflow message & overflow message extended.
I'd like to combine the fields into 1 and then design a new form to suit so that the memo text in the new db goes into 1 field only.
View 2 Replies
View Related
May 26, 2005
hi all,
I have made a form to run reports on a database, however, I need to add a field where the end user can enter an account number and the report will find the relavent fields. The problem is that the account numbers are stored as four different fields within the database (add_char1, add_char5, add_char9, add_char13).
At the moment I am trying to use a combo box on the form to display all of the account numbers, but I have no idea how to do this, I can only get one field of account numbers (eg only entries from add_char1 displayed in the combo box). I have read several of the posts in these forums, but not really found a solution. If anyone knows of a way to display multiple fields as a single column (so that the combo box produces a list including add_char1 to add_char13), or have any other ideas then please let me know!
Cheers
View 5 Replies
View Related
Mar 8, 2005
Hi Guys,
My table has three fields
pickup_carrier
delivery_carrier
rail_carrier
all the three fields share the same lookup source table.(TblCarrier)
Is there a way i can combine (uniquely) all the three fields to a common combo box with one field to select?
any one help..
Thanks,
BinJos
View 7 Replies
View Related
Mar 26, 2007
I have two reports, "Quick Glance Report" and "Note Report". The Quick Glance Report comes from a query called (ACE/ARB Table Query) and the Note Report comes from a query called (notequery).
I want to be able to have a calculated field from the "Note Report" in the "Quick Glance Report". Am I able to show a calculated field from one report into another report and how would I do this (If I can that is)?
View 1 Replies
View Related
Apr 24, 2007
Hi,
I have a table that has records with some data that is duplicated and some that is not. For example,
ID start_date finish_date customer
2 2-1-07 3-3-07 Jane Smith
2 3-7-07 4-30-07 Joe Shmoe
I would it to end up looking like this:
ID start_date finish_date customer1 Customer2
2 2-1-07 4-30-07 Jane Smith Joe Shmoe
With the start_date being the Min of both start_dates and the finish_date being the Max of both finish_dates.
Is this possible?
Thanks in advance!
View 3 Replies
View Related
Apr 25, 2015
The lookup has a two column dropdown: FirstName and LastName. This is for the field, Sponsor, however I want to concatenate the First and Last name and have those stored in Sponsor. How do I do that?
Note: I just did it in the Lookup query.
Now a related problem is when I make a change to the table (e.g. Lookup), the change does not show in the form. Is there a way to "refresh" the form? My work around was to delete and re-add the field...I'm sure that is not the correct way to do it.
And when I added the field, the caption comes along with it bound to the field. Is there some magic keystroke to break the bond?
View 8 Replies
View Related
Aug 28, 2006
Hey,
The database I am working on, I split a while ago to give it some security. Now i'm updating a related form, and i'm finding that if I delete and add fields in the BE, the FE fields (being the fields that I need to insert into the form so the data entered propogates to the DB) are not updated.
How do I update the FE?
Thanks.
View 1 Replies
View Related
Aug 3, 2015
In my table I have a numerical field for case numbers. Our case numbers are in the format of YYMM000000 (YY = 2 digit year, MM = 2 digit month, remaining 0's are consecutive case numbers). In order to properly list the case numbers in descending order, I have
caseyr: Left([CaseNo],2) (which pulls the first 2 digits being the year)
officeno: Right([CaseNo],3) (which pulls the last 3 for our part of the case number)
This was working fantastic until I had to enter cases from 2009, ex: 0911000587
I have set the format for the CaseNo field to 000000000 everywhere it is. It displays the 09 cases perfectly, retaining the zero, however, my left function ignores it. In the case number of 0911000587, it pulls 91 as the first 2 digits. So, in the form that I list my cases in order, the 2009 cases are showing up first (because they're pulling as 91 instead of 09).
Is there anything I can do to force it to NOT ignore the leading zero?
View 3 Replies
View Related
Aug 20, 2004
Hi,
I am trying to combine 2 text fields into a new field (concatenate) so that I can use it as the primary field.
Can anyone pls help.
View 3 Replies
View Related
Sep 18, 2006
I have an Access DB built that links to an excel spreadsheet. The spreadsheet changes so I cannot import and have to link.
Now the source of my spreadsheets is producing two spreadsheets with identical fields but different data. I need to combine the data from these two spreadsheets. Can I link them both and combine them in a query? How?
Appreciate any advise.
View 2 Replies
View Related
Jun 8, 2015
I wanted to combine 2 fields and a text in one text field on the access form.I have a field called "SentDateTime" and a field "Comment" I wanted to create it like this: MSD Message sent on 24/12/2014..I tried this formula in a unbound text field and it did not work:
Code:
=[Comment] & " " & "message sent on" & " " &[SentDateTime]
Code:
=J2& " " & "message sent on" & " " &TEXT(A2,"dd/mm/yyyy")
View 8 Replies
View Related
Nov 22, 2006
I've spent about an hour searching this site for email issues but most I found we how to send. I want to take existing data and make an email address.
I have a FName field and an LName field. In my email field I want FName.LName@email.com. the @email.com will always be the ending. Also where (on gotfocus) is the best place to put this code so it autopopulates after the FName and LName are entered?
Thanks!
Rick
View 1 Replies
View Related
Oct 3, 2007
Hey guys-
I have 2 tables- and need to find the record matches between them via their product code. Table 1 has the code as follows (and is considered 'the Master List')-
xxx-xx-xxx Where x is an integer.
Table 2 has split the 3 sections of the code out to 3 individual fields, like this-
Field1 Field2 Field3
xxx xx xxx
I need a query that will combine the 3 fields in table 2 to the same format as Table1 so that I can find the matches between them. I know this is a basic question, but I'm a learning newbie. How would I write that in a query? I don't think I want to write the format to the table- just include it in the query somehow...
Thanks!
View 6 Replies
View Related
