How to sort a field of data in a table that contains the letter S that is always in a specific place in the sequence? For Example:
13123S0-BAX
13124E1-ARZ
13122X2-THX
12134S0-GAB
Can I place a criteria in a calculated field?
[SellingWgt]*[SellingPrice] is ok but only
if [SellingUnits] = "lbs" or [tblSellingUnits].[SellingUnitsID] = 1
I have a very simple query on an accounts form to show a running transaction history.
Identifying from the TransactionID (shown for display purposes only - normally hidden) three or four postings make up one transaction.
Using TransactionID 10 as an example, I'd like to have a sum of total [Credit]-[Debit] and have the query display on one line (either at the top of £1,429, or at the bottom of £16,995) to identify this is in fact one transaction, having three posts.
Transaction 9 will have obviously have one total, as this is a single post.
11 the same as 10, by having one total Transaction Value either at the top of the row or bottom.
Hi,
Is there a way to search for queries that use specific criteria?
Let's say I have 60 queries in total, but only 35 of them use the "Province" field as criteria. The criteria is set to retrieve all records that are in Province AB, SK, ON.
Suddenly we need to also include Province MB to all of these 35 queries.
Is there a way to identify these 35 queries (all the queries use criteria in the "Province" field). These are the queries that would need to be modified to include "MB" as part of the criteria.
I hope my explanation is clear.
Thanks upfront for any suggestions!
BJS
Is it possible to have a query that uses criteria to pull data from a specific table?
For example: IIf([Result]'"Negative",(tblNegative goes here),IIf([Result]="Positive",(tblPositive goes here].
The tables are just text, but the query would be too long if it was used.
I am trying to use a DLookup to search to find what block goes into the production of an item.
In a table called "dbo_vw_MCE_job_with_materials", each item contains a list of the parts which go into its production in a column titled "Expr1". However there are always more than one parts to this, with varying names.
What I am trying to do is to use a Dlookup to only return the first value which starts with B (as the block number is always the only part on the list which begins with B).
At the moment my DLookup would look ike this:
Code:
MixTypeTxt = DLookup("Expr1", "dbo_vw_MCE_job_with_materials", "[item] ='" & Forms![Theta Input]![ItemNumbertxt] & "'")
However I think as the item column contains the same item number in 8 columns, with different values in the Expr1 column, Dlookup only returns the top value "A-CF0057" (which is irrelevant as far as I am concered) - whereas I am hoping it will be able to pull the "B1499" value from the column as it is the only one starting with a B
Is there anyway to specify which value the dlookup function would return, or is there any way to apply some sort of permanent filter on my table?
I have to add an Input Mask or force the user to enter in a specified format in a text field. The condition is, text should start with "A" or "T". The complete format is A-12345-67890 or T-12345-67890.
View 3 Replies View RelatedI've inherited a few Access DB monstrosities. One of which had ODBC credentials hard coded in the MDE which I had to recently update due to some server updates. I found an unsplit version of the Access DB on a development drive (j:db...) and updated the credentials hard coded in VB and updated the linked tables from the J drive path (dev) to the K drive path (prod). I then split the DB and the MDE worked fine for me. I quickly found out that it only works fine because I had J mapped still. End users do not have the J drive mapped (development) only the K (production). Without the J drive mapped to the actual dev path the Access DB simply doesn't load. No errors.
I then tried to find any references to J in VB and fix those. At this point I can't seem to find anything else that could be referencing the J drive but still the DB will not open without J mapped.
I am trying to create a mailing list of patients. Let's say I am creating a mailing list for February. I need the mailing list to consist of people who have had surgery in February from the beginning of the database, and people who have had surgery three months ago, so anyone who had surgery in November. I have created a form that has a button which is connected to a query, the form has a unbound textbox where I can enter the month in (2 for February). Then the query uses the datepart function to search for this month in their date of surgery. But this only gives me people for surgeries with february, how would I get people who have had surgery three months ago in the same query.
View 2 Replies View RelatedI have a drawing register database which notes the revisions for all drawings issued. One drawing can be revised several times so I have a details table which notes the revision letter, date issued and the notes for each revision. Using this table I created a max date query to show the latest issue date and revision for each drawing. This query is used for a transmittal form for issuing drawings. Both the form and report is working perfectly.
The TBLTransmittal consists of the following fields
ContractName
IssueDate
SubSup
DwgNo (multi-value checkbox)
Using the TBLDwgRegisterDtls or the query QRYDtldDwgReg (which includes the drawing titles) I would like to create a query that would look up the latest revision at the date of issue and show the revision letter.
I started a query combining TBLTransmittal and TBLDwgRegisterDtls and in the criteria for DateIssued of the TBLSDwgRegisterDtls (which is when the drawing was issued by the architect to the contractor) I put "Not > [IssueDate]". This is filtering the information to show revisions issued up to the date we (the contractor) issued the drawing to our sub-contractor but I need now to pull the max revision only from this information.
Before I go I will give you an example of what I want from the end result
Drawing BK2-02 rev. D was issued by the architect to the contractor on July 17th. This drawing was in-turn issued to our sub-contractor (Sub "A") this Saturday, August 9th. The same drawing was revised this morning and rev. E was issued to Sub "A" today, August 11th.
I want the end report to show that Sub A received Rev. D on 09-08-14 and Rev. E on 11-08-14.
I'm wondering if another max date query on this new query would work? I'll try and see what happens.
I basically have General Date field (e.g. 10/1/2014 6:34:11 PM) and I want to limit the results to only a specific month and only to show reuslts after 6PM. I tried everything and still stuck.
View 2 Replies View RelatedI am trying to get the max of an integer field if the ProjectNumber field is equal to the ProjectNumber field in the current record. I have used code similar to the one below before but it was on a date field, then add one.
Me.RevisionNumber = Nz(DMax("[RevisionNumber]", "dbo_ProposalLog", "[ProjectNumber] =" & Me.ProjectNumber), 0) + 1
The ProjectNumber field is a text field.
The forms in the code are subforms on a TabControl on a main form.
I have this code which needs to add together only the areas [Area] of records with a FloorNumber field value of 1 or 2 or 3 or 4.
At the moment the code works for one entry of 1 in the form frmRoomDetails. I'm guessing i need For Next or something like that but i don't know and also unsure of how to code it.
Private Sub FloorNumber_AfterUpdate()
If [Forms]![frmSiteDetails]![frmRoomDetails].[Form]![FloorNumber] = 1 Then
[Forms]![frmSiteDetails]![frmFloorsDetails].[Form]![Text8].Value = [Forms]![frmSiteDetails]![frmRoomDetails].[Form]![Area]
End If
End Sub
I have data for hundreds of stores. The data was pulled for the top 15 items by store, so I cannot obtain only the top 5 items that I need. How can I query this data to extract only the top (or bottom) 5 Subjects, by store, based on the percentage column?
StoreSubjectSalesSales %
1516Fiction56431.5-24.15%
1516Audio Unabridged1650.8-231.04%
1516History / Military History10081.1-29.99%
1516Role Playing / Graphic Novels14773.9-20.27%
1516Mystery13152.6-19.84%
1516Audio Abridged1785.9-141.84%
1516SciFi / Fantasy27535.3-7.93%
1516Juv Audio/Video1580.6-100.13%
1516Biography8103.6-15.89%
1516Sports7910.8-15.64%
1516Current Affairs / Law8141.9-14.34%
1516Reference7183-16.22%
1516Juv Non-Bk4585.9-25.02%
1516Science / Tech2961.4-33.98%
1516Movies / TV / Music / Dance3395.3-29.46%
1872Fiction307344.3-7.49%
1872Business134307.5-13.48%
1872Psych / Self Improvement100650.4-10.05%
1872Audio Unabridged29165.9-27.32%
1872Cookbooks57463.3-13.56%
1872Computers59235.7-12.37%
1872Regional59883.4-11.22%
1872Health & Fitness64713.8-10.29%
1872Maps19358.4-27.66%
1872Current Affairs / Law47927.1-11.08%
1872Travel Foreign42583.7-12.27%
1872Religion / Bibles80255.6-6.07%
1872SciFi / Fantasy67641.4-6.49%
1872Study Aids / Notes38299-11.24%
1872Games41745.1-9.79%
Hi,
I'm designing a database in which the primary key is a combination of the first letter of first name, the first letter of last name and the date of birth.
How can I do that?
Regards,
CS
I have two tables - one with parent Records, the other with child records. The ID links the two tables .I want to add a sequence number on the child table which resets at each ID change. What would be the best way to accomplish this?
Code:
PC_ID ID Name Address
P 1 Parent1 Address1
P 2 Parent2 Address1
PC_ID ID Name Address SEQ
C 1 Child1 Address1 1
C 1 Child2 Address1 2
C 1 Child3 Address1 3
C 1 Child3 Address1 4
C 2 Child1 Address1 1
C 2 Child2 Address1 2
C 2 Child3 Address1 3
I've got a table [Devices] with some number fields that are already in there for another purpose.
In a different exercise I will manually collect more information from a layout drawing and give each piece of information I collect a unique reference(this could be as simple as a PK ref) and attach a "type" and "location" . This will be done randomly in a batch exercise any time.
I need to put the above into access and hold it until I choose to individually allocate these records to the number field that already exists in the [Devices] table.
I would like to add "say" a combo/List box in [Devices] where I could search for the PK Ref which when selected against a number would populate the [Devices] table with previously unallocated information. I would like to keep any combo/List box down to unallocated PK refs to make records easy to find. I guess this a kind of cut and paste exercise.
I have a table with a field name compliance level which is filled with drop down list and having like this;
poor
good
excellent
IR
Now what I want is if i select poor from list then in query it generate -10 in query field named analysis, same if select good then generate 10 and so on means i will assign numbers for each field...
One can select only one item like, poor at one record entry...
After running a query is it possible to select just one record and place it on a report or print it? If so how do i go about it?
View 3 Replies View RelatedI do same thing each month and would like to make it easier/faster: is there a way to run many queries, one after another?
I cannot use macro, cause Run SQL is restricted to 255 characters and my queries SQL codes are longer than that.
Is there a way to run them in a row? Like run query1, wait till its finished, then run 2..3..4.
Have two tables: Assignment and StudentHeader - they are related by AssignmentGUID
Have the SQL:
SELECT StudentHeader.[Student ID], StudentHeader.GUID
FROM Assignment INNER JOIN StudentHeader ON Assignment.GUID = StudentHeader.[Assignment GUID]
WHERE (((Assignment.[Assignment Type])="Q") AND ((Assignment.[Assignment Number])=2))
GROUP BY StudentHeader.[Student ID], StudentHeader.GUID
ORDER BY StudentHeader.[Student ID], StudentHeader.GUID;
This returns:
Student IDGUID
al003072274
al154636303
al154636463
al595048272
al941626429
am180614426
am750033300
am750033462
I want a third field - Sequence - based on the Student ID and GUID, therefore
Student IDGUIDSequence
al0030722741
al1546363031
al1546364632
al5950482721
al9416264291
am1806144261
am7500333001
am7500334622
I am looking to make a table which adds a sequence number to my records.
Example data would look like this
Acc no Date
1234535 01/01/2013
1234535 05/01/2013
1234535 11/01/2013
9876543 02/01/2013
9876543 22/02/2013
9876543 01/03/2013
I want it to add a deq based on the account no then date in sequence oldest to newest.
Acc no Date Seq
1234535 01/01/2013 1
1234535 05/01/2013 2
1234535 11/01/2013 3
9876543 02/01/2013 1
9876543 22/02/2013 2
9876543 01/03/2013 3
How to extract or calculate the number of records in a sequence - e.g. in a table/query ordered by ID and date, the number of consecutive records by date for a given ID that have a value >= 50 in a given field. Is there a way of doing this purely within a query, or would I have to resort to a VBA loop through a recordset and keep a count of the consecutive records that match that criteria?
View 3 Replies View Relatedhave two table - TABLE 1 contains letter of the alphabet and a user assigned to each letter while TABLE 2 contains company name and user assigned to each company.What I'm hoping is if I change the user2 for letter A in table 1 all the company that starts with letter A in table 2 will have the user2 as the user assigned.
View 4 Replies View RelatedI need to export a currency field to include the pence but without the decimal place, is this possible?
It also needs to be 11 characters wide.
I have tried a number of formats and can get the decimal point removed but I am not getting the pence displayed.
ok here is what i want.. imagine a search box, just an empty text box that allows the user to type things into it...
there will be a simple table from the database behind it, here is the typical structure of a record
<Country>Poland
<Price1>60
<Price2>85
<Price3>95
in this table there will be say a 1000 of these records...
my user is going to have to quickly search through these records while on the phone to a customer, and quote one of the relevant prices associated with that country
what i would like is this most convenient system..
as the user types in the letter 'P' just below all the records with countries that begin with 'P' are displayed (with a scroll down arrow if needed)..as well as the 3 relevant prices with that record
if they then type an 'O' into the box (which will now hold 'Po') all the records with countries beginin with 'Po' will be displayed (eg Poland)
if they delete the 'o'. once again the displayed records below the box will return to just the countries begginin with 'p'
all that is needed is for the records to be displayed, thats it.. but the adding and deleting of the letters within the text search box will need to instantly manipulate this list..
i have posted on other forums, and have been told this can be acheived in access, is it hard?