I am trying to return the MIN in a numeric field (single result) from my database and I have no issues doing this BUT the problem is that I also need to display additional fields in order to make the result useful. The additional field can't be grouped by b/c that changes the result of the MIN function.
View RepliesI'm creating a database for a travel agency. There are 2 tables, one for customers and one for their bookings. I have established a relationship between the tables so the AutoNumber for customer ID links to a field on the add booking table.
In practice though, it would be easier to enter the customer NAME into the booking table and have it retrieve their ID that way to link them. This seems like I am missing something simple but can't fathom a way to do it.
The other factor is obviously more than one customer will have the same name so I may need to link first name as well to differentiate?
I am trying to use transferspreadsheet to import access worksheet and then I want to append additional fields. I am able to import the excel sheet into access, but need appending the other fields.
Code:
Dim fd2 As FileDialog
Dim xlapp As New Excel.Application
Dim xlsht As Excel.Worksheet
Dim xlWrkBk As Excel.Workbook
Dim db As DAO.Database
Dim tb2 As TableDef
Dim fdx, fld As DAO.Field
[code]...
I have a database that will take lots of data entered by an employee and calculate some additional data and generate quotations. Within each quote there is a possibility for 15 different metals (5 Precious Metals, and 10 Base Metals). There is also 5 fields that need to be filled out about each Metal (What the metal is, the market being used, weight, whether it is included in a different price, and the price). I currently have 75 fields to address each Metal and their 5 fields respectively. Is there a better way to Normalize this data, and accomplish what I need accomplished? I want at the form level the employee to tell the database whether they want to add a Precious Metal, or Base Metal, or Move on to other data entry.
Here is a Screenshot of the design view of one of my tables with too many fields : table screenshot1.PNG
In the code below I am creating a record with the INSET INTO statement and then Updating with additional info in various fields. it is not working the way I thought it would, so I am trying to create the record in it's entirety.
Code:
SQL_Grade_GUSD_ID = "INSERT INTO Grades (GUSD_Student_ID) VALUES (" & Me.GUSD_Student_ID & ")"
SQLM1_1_ELA = "UPDATE Grades SET Grades.Subject = ""BM1(ELA)"""
SQLM1_2_ELA = "UPDATE Grades SET Grades.Type = ""Exam"""
SQLM1_3_ELA = "UPDATE Grades SET Grades.Score = ""0"""
SQLM1_4_ELA = "UPDATE Grades SET Grades.Nam = ""GUSD BM-1"""
DoCmd.RunSQL SQL
[Code] ...
I am running to syntax problems when I try to USE the INSERT INTO to create the record with all the info in one statement.
Code:
SQLM1_1_ELA = "INSERT INTO Grades ( GUSD_Student_ID, Subject, Type, Score, Nam ) " & _
"SELECT (" & Me.GUSD_Student_ID & ")"" AS GUSD_Student_ID, ""BM2(ELA)"" AS Subject, " & _
"""Exam"" AS Type, ""0"" AS Score, ""GUSD BM-1"" AS Nam " & _
"FROM Grades"
I am Getting this error:
Syntax error (missing operator) in query expression '(12345)" AS GUSD_STUDENT_ID,
"BM2(ELA)" As Subject, "Exam" AS Type, "0" As Score, "GUSD BM-1" AS Nam From Grades'
Is there a way of merging 2 fields together to create an additional field
my database consists of 4 main tables (in order of relationships)
*HeadOfficeDetails
*SiteDetails
*ContainersOnSite *Contracts2015-2016
For example;
Account Reference: TEST
Site Number: 001
and the field i would like to have;
Site Reference: TEST/001
I would also like that when i add a new site to that account i will have TEST/002....
I am trying to calculate the difference in dates between two fields. How do I find the difference in days between field one which contains the date 04/12/2011 and field two which contains the date 04/12/2013? I have tried to use the datediff function, but it keeps telling me it doesn't recognize the field name, even though the spellings correct.
View 3 Replies View RelatedThe main table for the database I am working on contains the following fields:
ID
Mfr Control Number
Initial or Follow-Up
Follow-up Number
Suspect
Date of Initial Email
Date Received
Date Submitted
Date of Report
Serious
Brief Description
Causality
Notes
With some additional qualifications I wanted to find records that had an intial report but no follow-up. Which translates too I want records that are unique in the Mfr Control Number field (no duplicates).
I am trying to build a query and keep getting hung up on the unique aspect of fields. I started by trying to query only "Mfr Control Number" fields that are unique (no duplicates). As best I can figure for some reason I can not add any additional fields to that query. My current query is set up in the query build table such that I have added "Mfr Control Number" in two columns. The first column in Total I have "Group By". In the next column I have set Total to "count" with a criteria of 1. If I try to add any other fields from my table than I seem to lose those unique results. But I need to further filter to get the exact information I need.
I want criteria on the "Initial or Follow-up" field to only bring "initial"
I want criteria on the "Serious" field to only bring "serious"
I want criteria on the "Date of This Report" field of "<Date()-"15""
Is there some way to take the results of that initial query to then build a another query based just on those records? I could then apply the further criteria and run my report. Or is there a way to do this in one step?
I've tried to make clear my intention but know it can be difficult to get this kind of stuff down in writing in a clear fashion. I have to be careful to keep information confidential also so some of the details are vague.
Some of my collegues have been experiencing this problem with a very simple database i knocked up for them. First off some background. I have a main form (frmproperty) with 3 subforms linked to it(frmroomdata, frmlocation and frmsample). by a property code. Two of these subforms are linked (frmlocation and frmsample) these are linked by location code which is an autonumber.
These are laid out so that you fill in room data first, then location data then finally sample data. However after the location data has been filled out and a user attempts to move to the first combobox on the sample subform a message pops up saying that another user has altered the database and would you like to save changes (Impossible as they are using the database on a local drive). After this happens they reopen the form and in all the frmsample fields is "#deleted"
This doesn't happen every time...infact over the last 2 days i have been entering data myself to try and get this error with no luck. Has anyone else come across this and know how to fix it?
Thanks in advance
Matt Collins
im working on a report at the moment that will generate a standard NIP (nutritional information panel, similar to whats on the back of pretty much all packaged food products, see link below for an example)
[URL]
this in itself is pretty easy, however depending on which market we're selling to, different countries have different requirements for which data is to be shown, ie some markets might require we show how much sodium but not how much fiber, others might require us to show how much carbs and sugars but not how much vitamin C and so on (all up there are 16 different variables for nutritional info, due to size requirements on our packaging we cant simply display all 16 all the time)
at the moment im designing a form which has a drop down box (for the user to select a product) and checkboxes for each of the 16 variables (kilojules, carbs, iron, trans fats, sat fats, etc) following this ive got a report that has all 16 variables on it
i can make the variables that arent ticked on the form invisible (by adding a bunch of IF or Case statements, on print of the report, that determine whether the corresponding checkbox for each variable has been ticked and setting the "visible" attribute to true or false accordingly), however when they are invisible there is still a blank space where the field is meant to be ive got all the fields grouped together, is there any way of making fields invisible and then pushing all the visible fields together so there are no blank spaces?
Where in Access 2010 I can modify the displayed fields of a Combo Box on a form. I need to format the display window and add criteria to one of the fields. Can't remember where I set this up and recently upgrading from Access 2007 isn't working.
View 5 Replies View RelatedI have a Table1 served by Form1..It is a list of: UnqID, process, quantity, totaltime(in seconds).I want to click on a record to bring up a filtered Form2 with the chosen record on it.What I want to be able to do is to now split the quantity (and the time) and put these new records back into Table1 and delete the original record
EG
ID1,10,write a report,2400
I want to delete this and replace it with two (or three/four etc) replacements, but still adding up to 10 quantity and 2400 seconds so that the new data could be:
ID2,5,write a report,1200
ID3,5,write a report,1200
My initial thoughts are to create a holding table to:Append filtered data on Form2 to a holding Table1hld (i don't know how to do this) delete data in Table1.then enter the new quantities into a holding Table2 (that I will input myself) and then append (through a series of queries back into Table1).The first problem is how to append (and subsequently delete) the filtered record from Form2 to Table1hld.
I have a Form to Display Projects that has a SubForm displaying Sub-Projects related to them. The main Project Form has a Combo Box which allows a user to select a ProjectID to display. The SubForm has a ProjectID Field as well. When the Project ID is selected in the Main Form, only the appropriate Sub-Projects are displayed in the Sub-Form.
On the SubForm, there is a Field named ActionID, which uses the following VB Code to limit the availability of the Fields in the SubForm that apply to Dates. If the selected Action is Install, only Install related Dates should be available. Likewise, if the selected Action is Remove only Remove related Dates should be available.
Code:
If Me.ActionID.Column(1) = "Install" Then
Me.AssetInstallMonth.Enabled = True
Me.AssetInstallYear.Enabled = True
Me.AssetRemovalYear.Enabled = False
Me.AssetInstallMonth.Locked = False
[Code] ....
Everything seems to work as expected until there is more than one Sub-Project is the SubForm and they do not contain the same Action. In these cases, it appears as if the Sub-Projects in the SubForm are not being treated as individual records. If Sub-Project is Added or Modified to have an action of "Install", all records are treated as Install Records (only Install related Dates are available), regardless of their content.
Is there a way to update the properties for only the row that has been selected?
Hi,
I am trying view the query output's in different text on the form and I do not want to see them again in query window.How do I hide this.. comments
???
I have a query that is linked to a data table. The values entered in the data table are fine and display as expected. When I run the query, the results of one of the columns show Chinese like characters instead of the actual value. But, when I filter that column by choosing 'does not contain...' the actual cell contents display. I have 2 other identical queries reading from the same table and they do not display funky.
View 3 Replies View RelatedI have the following query which returns 2 columns, where 2 fields are summed :-
Code:
SELECT Sum(Stats.[No of Invoices Checked]) AS [SumOfNo of Invoices Checked], Sum(Stats.[No of Incorrect invoices]) AS [SumOfNo of Incorrect invoices]
FROM Stats
WHERE (((Stats.Period)=[Forms]![frmMain]![cboSingleMonth].[Value]));
This displays :-
345 - 988
How can I have the data displayed as
345
988
when I run the query.
I have a master table with all of my Site Names in it. I have a collection table that when a barcode is scanned it records the site name as being received. I built a query that counts the number of times the site name has been received.
What I want to do is list all of my sites from the master then display the count next to the corresponding site name. This will identify those sites that did not send in an item. Is there criteria in the query I could use so that 1) I could list all of my sites from the master table and then the count can be displayed from the collection table next to the correct Site Name?
I have a database where a column holds the links to files inside a folder. I had a macro running all day to get from a .pst file all the emails inside a database. Now I made a mistake by later changing the whole folder to a different drive. I don't want to import them again as was thinking to use a query to add the new part of the drive path to the column. How can I make a query to change the following:
C:Tempemail archiveemail.msg
to:
D:DataIncomingemail archiveemail.msg
Hi all, wondering if anyone can help me.
Im trying to a run a query to see the results of my table. but i am experincing problems, when use the SUM function.
I have constructed a query with three fields.
1) QTY.
2) Cost
3) Total.
what i would like is to be able to Multiply QTY & Cost to get the total.
so whan QTY Is 0 and Cost is £0.00. the query recongises it and changes the total should then be £0.00.
any advice on enabling the query is welcome.
I have a Number field with value as
DSum function where the criteria is
Textfield which works well.
=DSum("[MCRef]","Miracle_Cloth_Main","[Cust_ID]='" & [Cust_ID] & "'")
It doesn't show the SUM when the type of
textBox is CURRENCY
Can anyone suggest IF Ineed to change the syntax ?
The idea is that I have a table with products. It is joined to another table that has each products ID and then a series of fields that correspond to each month of this year (so 12 fields). These fields contain how many of each product sold in that month. However some products did not appear in inventory until a few months into the year, so they have zero's for those months in which they didn't exist yet.
What I need to do is find the first month that each product went on sale, and pass that field back to my main table to do calculations with.
First I tried to do with with a query, but I ran into a road block and realized that maybe a query wasn't best as I likely needed a loop. So I started writing a function at that point... but it is obviously non functional.
As an example, if I had a product like this:
Product: X
SalesID: 1111
Price: 9.99
Month 1: 0
Month 2: 0
Month 3: 1582
Month 4: 2790
Month 5: 4501
Month 6: 4210
Then the idea of this function would be to look at Month 1, see if it contained a zero. if it did, move to month 2. If it doesn't, then send whatever that value is to a new field in the database.
So in the new table, I would have:
Product: X
SalesID: 1111
Price: 9.99
Month 1: 1582
Month 2: 2790
Month 3: 4501
Month 4: 4210
I have a small database that tracks staff training. Main tables are:
tb_staff
tb_courses
tb_courses_done
1 staff can take many courses. I want to introduce booking - staff can book a course but not necessarily attend. Can I just add a table, say tb_course_booked and use an update query to add to tb_courses_done as needed? Would that work?
Thanks.
Is there anyway to turn off the rounding function in calculated fields - I need to divide a currency amount by a whole number to calculate the number of full coin bags that will be needed (bags contain different totals according to the coin value) - a whole number.
Then the result is used to calculate how much loose coin will remain.
But regardless of the field type and/or the format, the number rounds when I reduce the decimal places to zero. I need the result to be the whole number and to calculate as the whole number.
I a trying to list all fields from ALL tables in a certain DB.I am using the tableinfo function. However, because the output of the immediate window is limited to 200 lines, I can only see the last 200 fields. Is there a way to export this in another way than the debug.print procedure, so I can bypass this limitation?
Code:
Public Sub showtablefields()
Dim db As Database
Dim tdl As TableDef
[code]...
Using Access 2010
I'm an expert user in Excel and am trying to learn more about Access, so I relate many of the functions to what they are called in Excel. And what I'm trying to do right now is count the number of fields in a specific record where the value is a specific result (CountIf).
For example, I need to return the number of fields in a specific record where the value is "Meets Requirements". The specific record will be determined by the Name, Contact, and Email fields (which together comprise the Primary Key).
Is there a way to count the values in multiple fields that equal "Meets Requirements"?
Can I import data into a table that already has data in it? I want to add this new data to the existing information.
View 2 Replies View Related