Hello,
I have a table with 2 fields - WeekID(autonumber) and WDATE(date/time). What I want to be able to do is to enter a value for the first date and then have the next 25 entries automatically entered. Each entry is to be 1 week greater than the previous entry. Ultimately giving me a list of dates, numbered 1-26, incremented weekly.
I assume the DATEADD function is to be used but i cannot determine how.
Any help / pointers is greatly appreciated.
Thanks
I need to insert the parameter dates for a query into a report. I have tried all the methods I can find but none seem to work. I think its to do with the way my queries are structured.
I have a basic query (q1)! Based on this is a grouping query (q2)! Based on this grouping query is my report. I have set up a form prompting for the parameter dates. This all works ok and my report displays the correct data (between the dates entered). The problem is that I cannot get the report to show the parameter dates.
I am at work, and I have acquired a database that prints labels. They now want the database to be coded so that after certain labels are printed the database will print a blank label. I have the code figured out as a Do While statement in order to print the blank label. The problem I am having is that I am trying to use the Insert Into command to insert the filepath into the table that adds the blank label.
|DoCmd.RunSQL "INSERT INTO Rod_tmakLabels ( Print, [Order] ) SELECT Yes AS Expr1, 'Rods Labels' AS Expr2"|
If I run the above command, it just adds the text "Rods Labels" at the end of the table. Is there anyway with the INSERT INTO command that I can insert the new label between the 2nd and 3rd row and add another row? Or is the command designed only to add a new row to the end? I haven't had any luck searching for this yet.
I would like to enter a week dates forward after looking up the last entered date. i have working codes that looks up the last date and increments it by one and enter the date into a new field.
Here is what i have:
Private Sub Form_AfterUpdate() Dim dtmNextDate As Date Dim dtmLastDate As Date Dim SDate As Date LastDate = Nz(DMax("[SDate]", "schedule"), _ DateAdd("d", -1, Date)) NextDate = DateAdd("d", 1, LastDate) CurrentDb.Execute "INSERT INTO Schedule(SDate) VALUES ('" & NextDate & "');" End Sub
enter dates into 5 new fields instead of one using a loop maybe.
If you want to use a "DoCmd.RunSQL "INSERT INTO" command to insert data in a table and the data to insert comes from a table and a form, could this be done in one pass?
So...writing a record wit 4 values from table1 together with a additional value from a textbox in table2 as 5 values.
I have a parent table (tblLabels) and a child table (tblRevision) where the revision history for the parent table is kept.
The parent table is populated via an excel import and may have several records imported at once. Instead of having the user manually enter a new record note in the child table for each record imported into the parent table, I've created a form that collects the necessary data (date, person who added the record, person who authorized the record, and notes) and then creates a revision history for each new record.
This is what I have so far:
Code:
Private Sub cmdAddNotes_Click() Dim strSQL As String Dim RevisionDate As String Dim RevisionRevisedBy As String Dim RevisionDesc As String
[Code] ....
When I run the code nothing happens. No error, no new records create, etc. My suspicion is that I may need to use an INSERT INTO query instead of an UPDATE query, but I'm not sure how to go about matching up the foreign keys if that's the case.
I have a small database with 4 tables that I am using for the current problem. The tables are call, parents, mailman, orders. Call and parents are related by the call ID (a primary key in the Call table.). Mailman and orders are related by a Unique Id (a primary key in the mailman table.).
Forms involved are frmmain and frmsub.
Frmmain contains the call table information in the main form and parents information in the subform.
When a user enters a call with call ID and enters the operator name and parents information in the sub form, When a user clicks the OK button on the main form, necessary changes should take place if they enter the case type in the sub form part of parent information as ‘missing information’ or ‘missing link’ then the parent information with fields first name, lastname, case type, operator information should be inserted into mailman table in appropriate fields.
Simultaneously a record should be inserted into orders( after the record is first inserted into mailman, since both tables are linked with unique id) with the following information. Orderid being autonumber. Uniqueid from the mailman table. Orderdate system date. Ordertype should be “Mailman”
How to copy a column from one table and insert it into another table in the same database
Hi, All,
I have two tables (old and new) sitting in the same database. The new table is the result of 'data cleansing' done by an external company. In the process (export and import via excel) two memo type colums in the table were truncated in excel.
To make the new table usable, I must therefore now copy/insert the two memo columns from the old table into the new table.
Both tables are already Access tables and sit in the same database. Both tables, of course, have the same number of rows.
I tried to high-light one column in the old table, clicked copy, then high-lighted a blank column in the target table, then clicked Paste, but got error msg: "This text is too long for this field. Try copying a shorter text", as if I had wanted to copy the whole column into one cell rather than one column into another column of equal length.
Newbie question: I have a database where we track dispatched with two tables, one called Slots, the other called Dispatches. In the slots table there are 2 fields, one called Week (which hold the Monday date for each week in the year), the other called Available (in this field we want to manually enter a number that will tell the system how many dispatches we can do that week). We want to automatically take the number from the Slots.Available and enter X number of blank records in the Dispatch table with the monday date. How can this be done? Then we will create a form that can be filled in with the balance of the information. Thanks
On the creation of a reconrd in the main table I'm trying to auto fill a sub table with standard information for the user. This is what I'm using to insert into the sub table:
I have an access database that contain 2 tables and 1 form :
table1 named customers (custid-custname) table2 named sales(salesid-custid) form named form1 contain (salesid-custid) *note:in form1 custid is combobox
What i need is when i select an customer from combobox the column custid in sales table get custid not custname
I use this sql statement to get combobox items from customers table
Anyway, I would call myself intermediate level at best with Access. I never expected to have to do so much with it, but when my bosses found out I could do Access basics, they began demanding more and more.
I manage an EMR from which a datapull occurs on discharge to various access databases.
They wanted me to add a triage patient data pull to track what procedures are being done to triage patients. So I built a database with the following 2 tables.
tblTriageVisits and tblTriageProcedures tblTriageVisits has the following fields -Patient ID -Export Time
[Code].....
The tblTriageVisits stores all the patients triage visits. The other table stores what was done to each patient on those visits
It should also be noted that this EMR exports times in number of seconds since 12/31/1975, so TimeProcFMS is the number of seconds since that date for example.
The pulls work great and the duplicate record elimination method works great.
I have to design a couple of different reports based on this data and one in particular has pretty much flumoxxed me.
I need to make a report that lists each patient by date they were there and then shows what was done to them. I have tried several different variations of this as both queries and reports. The report breaks down when I try to get it to show just those procedures that would be for that triage visit. I got to the point where it would group by day and then sort the patients alphabetically. Then I added in the visit fields. And it would put all triage procedures for that patient from all their visits in each visit.
So, I was like great, let me filter out those that don't occur between the two dates, admission and discharge
I used the following two formulas (the first one on the procedure name field the second on the procedure date field)
=Switch(DateAdd("s",[TimeProcFMS],#12/31/1975#)>=[Admission DateTime OB DateTime] And DateAdd("s",[TimeProcFMS],#12/31/1975#)<=[LD Discharge Date/Time DateTime],[Procedures FMS])
=Switch(DateAdd("s",[TimeProcFMS],#12/31/1975#)>=[Admission DateTime OB DateTime] And DateAdd("s",[TimeProcFMS],#12/31/1975#)<=[LD Discharge Date/Time DateTime],DateAdd("s",[TimeProcFMS],#12/31/1975#))
And they work, sort of. They do eliminate those procedures that occur outside of the date range I am looking for. However, the access report still leave spaces in the report where the filtered out items would be.
Did I approach this problem correctly? And if so, how do I get rid of those spaces?
Note, I can read and mostly understand SQL, but am not comfortable working in it yet. And the Access SQL box gives me a headache, it is so jumbled up.
I have created a booking system for a set of resources for schools. Most schools have a membership which entitles them to 2 free sets. I have a booking form with a membership subform (membership table), and a booking details subform (kitloan table).
Once a school is selected on the main form, the membership subform shows the most recent record for that school based on schoolID.I want to display the number of sets they have already had within their membership period (can start at any time of the year, and lasts for 1 year) on the membership subform, so we know how many free ones they have left.
I therefore need to count the number of KitBkID (ID of the booking) in the Kitloan table where SchoolID = the SchoolID displayed on the membership subform, and the DateOut (booking date on kitloan table) is between the DateJoined and DateRenewal displayed on the membership subform (from membership table).
I can do this with a query which works when run and provided with the parameters SchoolID, DateJoined, and DateRenewal.
SELECT Count(Kitloan.KitBkID) AS CountOfKitBkID, Kitloan.SchoolID, Kitloan.DateOut FROM Kitloan INNER JOIN Membership ON Kitloan.SchoolID = Membership.SCHOOLID GROUP BY Kitloan.SchoolID, Kitloan.DateOut HAVING (((Kitloan.SchoolID)=[Me].[SCHOOLID]) AND ((Kitloan.DateOut) Between [Me].[DateJoined] And [Me].[DateRenewal]));
What I can't do is get it to run on the form and take those values from the form.From the searching I've done, I'm thinking a DCount should be the way to go, but I cannot get the criteria right. I created a query (KitloanCountQry) so that criteria could come from both the kitloan and membership tables.
SELECT Kitloan.KitBkID, Kitloan.SchoolID, Membership.DateJoined, Membership.SCHOOLID, Kitloan.DateOut FROM Kitloan INNER JOIN Membership ON Kitloan.SchoolID = Membership.SCHOOLID;
I have put the DCount as the control source for a textbox on the Membership subform (but have tried it in VBA too): =DCount("KitBkID","KitloanCountQry") This works but obviously gives me the total for all bookings.
[code]....
Although I have to admit to getting lost in the syntax. This produces #Error.
Hi I am creating an employee db, with fields including employeeID, surname, firstname, partnerSurname, partnerFirstname, partnerID. As some partners work at the company, I want the partnerID to list (thorugh a value box) employeeID's that match surname to a partnerSurname and firstname to partnerFirstname. Is this possible? Any help greatly appreciated. Thanks in advance.
Insert into tableA (x,y,z) = (SELECT x,y,z FROM tableB) WHERE NOT EXISTS (select x,y FROM tableA,tableB WHERE tableA.x = tableB.x AND tableA.y = tableB.y)
Basically I want to insert data in tableA from tableB if it does not exist in tableA yet.
hi, i want to convert some below mentioned format into msaccess tables.
the exact scenario is that i have some text files from old applicaiton (pascal based) which is no longer working due to hardware compatibility problem...
which has generated some text files under below mentioned format...
I have a table which contains the following fields: WORKER,SUB SYS,JAN-08,FEB-08,MAR-08 I want to insert into this table each month the data from the second table for that month can someone tell me how to do this...each time I try the secnd table data TOTAL strarts a new row below the ones that are already there...hard to explain. I tried this and it didn't work:
INSERT INTO [ORACLE SORA ALERTS BY WORKER] ( [JAN-08] ) SELECT A.TOTAL FROM A INNER JOIN [ORACLE SORA ALERTS BY WORKER] ON (A.SYS = [ORACLE SORA ALERTS BY WORKER].[SUB SYS]) AND (A.WORKER = [ORACLE SORA ALERTS BY WORKER].WORKER);
WORKER SUB SYS JAN-08 FEB-08 MAR-08 0000A CRT 0000A ENF 0000A FIN 0000A RAP 000AT CRT 000AT ENF 000AT FIN 000AT RAP 000AX CRT 000AX ENF 000AX FIN 000AX RAP I have a second table which contains the following fields: WORKER,SYS,TOTAL
WORKER SYS TOTAL 0000A CRT 1 0000A FIN 21 000AX CRT 1 000AX ENF 2 000AX FIN 11
What I want is to move the numbers in the TOTAL COLUMN INTO THE MONTH/YEAR COLUMS IN THE FIRST TABLE. Would look something like this:
WORKER SUB SYS JAN-08 FEB-08 MAR-08 0000A CRT 1 0000A ENF 0000A FIN 21 0000A RAP 000AX CRT 1 000AX ENF 2 000AX FIN 11 000AX RAP
When I run this query I want the data inserted into an existing table in the correct place. It is inserting the data in the correct column but not the correct rows. Please see attached screenshot. Can you please show me what I'm doing wrong.
INSERT INTO UNDISTRIBUTED ( Jun2003 ) SELECT Sum(CCur(a.AMT_TO_DISTRIBUTE)) AS AMOUNT FROM NOLDBA_RECEIPT AS a, NOLDBA_INT_CASE_STATUS AS b WHERE (((Trim([a].[id_case])) Is Not Null) AND ((a.ID_CASE)=[b].[case_id]) AND ((a.CD_STATUS_RECEIPT)='H') AND ((a.DT_END_VALIDITY)=#12/31/9999#) AND ((Exists (select 1 from NOLDBA_RECEIPT k where a.dt_batch=k.dt_batch and a.no_batch=k.no_batch and a.cd_source_batch=k.cd_source_batch and a.seq_receipt=k.seq_receipt and k.ind_backout ='Y' and k.dt_end_validity =#12/31/9999#))=False)) OR (((Trim([a].[id_case])) Is Null) AND ((a.CD_STATUS_RECEIPT)='H') AND ((a.DT_END_VALIDITY)=#12/31/9999#) AND ((Exists (select 1 from NOLDBA_RECEIPT k where a.dt_batch=k.dt_batch and a.no_batch=k.no_batch and a.cd_source_batch=k.cd_source_batch and a.seq_receipt=k.seq_receipt and k.ind_backout ='Y' and k.dt_end_validity =#12/31/9999#))=False) AND ((b.CASE_ID)=(select min (y.case_id) from NOLDBA_INT_CASE_STATUS Y, NOLDBA_INT_CASE_MEMBER Z WHERE A.ID_PAYOR=Z.MEMBER_ID AND Z.RELATION_CODE IN ('A','P') AND Z.CASE_ID=Y.CASE_ID))) GROUP BY b.IV_D_DO_CODE ORDER BY b.IV_D_DO_CODE;
I have a form that contains a textbox and a listbox. The listboxs rowsource is tblSPNZ. The table has 2 fields 1 is and autonumber and the other is where I want the results to go.
How do I make a command button populate the table? Avoiding putting any results into the autonumber field?
HI, Is it possible to save data in a table, just doing a check on the value of a combobox?
I need to insert a Num_CC value that I got from a table called TB_ANAGRAIFCA, in the table called TB_ALLEGATI. But the value of Num_CC depends on the value selected in a combo box. So I have to save the Num_CC related to the Provider selected in the combobox namade Nome_Provider.
Any suggestion?
I tried to draw in the form interested a textbox invisible, but I don't know how to retrieve the data. Please, HElp me ....
In my VBA code, i am trying to move some table records from table1 to table2. Both table1 and table2 has exact structures.
In the INSERT statement for table2, what is the easy way to insert a record from table1? I am trying to avoid having to specify each field name in the INSERT statement. Because, i have 100 fields in table.
Is there a way to INSERT a particular record from table1 to table2 without having to specify field names in the INSERT statement like in below example?
I currently have 10 tables in my database (All with the same colunm names and formats) however i want to click a button that will put all the tables into TEST_DOC, instead of doing it one by one. To do this this i would like some code either in SQL or VBA that will do this.
