Hi,
I'm new to Access and I've run into a little problem. I've created a table with a primary key (autonumber) which will contain 1520 records. At present I only have data for the last 8 records. Ideally I would like to create 1512 blank records, with only the autonumber entered e.g. 0001, 0002, 0003 etc.
Unfortunately I cant add the data that I have got to 0001 - 0008 as it relates specifically to the primary ID.
Thanks.
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 am working on an electronic catalog for my company. I am populating the database and I'm trying to speed the process. This is what I want to do. This database deals with cars and trucks, I would like to design a form where I can enter:
1989-1995 Chevy Malibu, etc.
Once the form is full and I save the records it will create 7 individual records, one for each year.
1989 Chevy Malibu
1990 Chevy Malibu and so on.
Any ideas?
Can someone tell me how to insert a blank row after every 4th row of data?
Here is the query:
SELECT B.NAME, B.WORKER, C.[SUB SYS] INTO D
FROM B, C;
data comes back like this
NAMEWORKERSUB SYS
GORDON N PAUL0000ACRT
GORDON N PAUL0000AENF
GORDON N PAUL0000AFIN
GORDON N PAUL0000ARAP
GORDON N PAUL000ATCRT
GORDON N PAUL000ATENF
GORDON N PAUL000ATFIN
GORDON N PAUL000ATRAP
I want it like this
NAMEWORKERSUB SYS
GORDON N PAUL0000ACRT
GORDON N PAUL0000AENF
GORDON N PAUL0000AFIN
GORDON N PAUL0000ARAP
GORDON N PAUL000ATCRT
GORDON N PAUL000ATENF
GORDON N PAUL000ATFIN
GORDON N PAUL000ATRAP
I have a couple different reference files that get updated each week. Sometimes there are missing data elements, so I'd like to structure a select query to show me those records that have blank elements but I'd like the similar records to be pulled in as well, so I can make a determination as to how to populate the blank records..
See attached example: I have a client ID reference table that gets populated with forecast owner names (individuals responsible for the customer) from a couple of different sources. Sometimes there are names attached and sometimes the field is blank.
How can I structure a query to show me just those Client ID's that have multiple entries with blank AND non-blank forecast owners? I'd also like to exclude single/multiple records where there are only blank records...
OK, I have been searching around on the fourm for over an hour now...I give up. If this is some where else, I am sorry.
I have a database of maintenance data. There are several columns that are usually filled in, some records have some columns blank. They are formated text because they hold letters and numbers (see pic).
I am using a form to query the table...no problem. The form has text boxes the user filter down the data
The problem comes are with the results of the query. Any record that has a blank column is not retuned. I am using "Like" so that the user can enter in partial codes. I know "like" won't return "null" records.... Help!
Things I have tried:
1)IIF(form field is blank, return table field, else use like command to filter) - returns nothing!
2)Like "*" & [Forms]![Fleetwide_data_Request]![MAL_CD] & "*" returns all records without blanks (i.e. missing data)
what else can I do?
Thanks
How to insert x number of empty rows at once in a query? All fields allow null.
Say, if I wanted to "resize" the table to 3000 records...
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.
Hello all!
I have a database that needs to post records that are joined from three different sources into a table. The query is done, and I get about 1,489 records out in 4 different states.
What I need to do is make a table with these records. Furthermore, it must be separated by state, whereas if there are less than 1000 records for each state, it must insert blank lines until it reaches then next thousandth (sp?) row (i.e. 1001, 2001, etc.), and then start posting the next state.
For example, AZ has 420 records. There has to be 580 blank lines before the query can start posting the next state, CA. At row 1001, CA starts posting, but there are only 200 records for CA so there must be another 800 blank lines before moving on to CO at row 2001, etc. etc.
Anyone have any ideas on this? Thanks!
I have a main report / subreport relation, just want to have a fixed length of each printout. e.g the total number of lines of the subreport should be 8 lines. However, lots of subreports contain records less than 8. I would like to know how to insert of blank line in the subreport depending on the records with content at each print, in order to make the total number of lines in each subreport is 8 each print!!
View 1 Replies View RelatedI want to find duplicate records based on FirstName and LastName and delete the duplicate. Also, I want to delete any records which have a blank FirstName and LastName.
How can I do this?
Thanks,
Dave
Let's say I have three columns; apples, berries and bananas. I need to capture, either using the GUI or the SQL if any of those are not null, then bring that row back. So the rows that come back from the query are only ones where one of those three columns is not null.
In excel it would be if(or(apples<>"",berries<>"",bananas<>""),X,Y)
Can anyone help?
Have searched the net with no luck. This is my problem.
I am trying to use a multiple INSERT string to create records in a table, from a VB application and using ADO to do it.
If I send the single INSERT it works fine, but if I try to send more than one in the same string I get a 'MISSING SEMI COLON AT END OF STATEMENT' error.
I have tried putting one at the end of each statement, then just at the end of the INSERT statements string, but still get this error.
Can Access actually handle more than one SQL instruction at a time?
Thanks for any help.
I'm trying to insert multiple rows into a table at once, but I'm having problems.
I've tried a syntax like this:
INSERT INTO [TABLE] ( [FIELD1], [FIELD2], [FIELD3] ) VALUES
(( '1', 'A', '1' ),( '2', 'B', '2' ));
..but with no success.
On a site I even found this other way, but with no success either...
INSERT INTO [TABLE]
SELECT '1', 'A', '1'
UNION ALL
SELECT '2', 'B', '2';
Is it possible to insert multiple rows and I'm just missing the syntax, or is it not possible with Access?
I have a form with a print button. When pressed it runs of a set of labels between 2 dates. I wish to keep a log of the print jobs that are done. Storing the date and time that the print was done, who did it and (The bit I can't figure) the date range of the print job.
I am trying to make an INSERT statement with 2 queries. 1 for the oldest date of the print and one with the most recent date.
INSERT INTO PrintHistory (RangeStart, RangeEnd)
VALUES (
(SELECT TOP 1 [Bookings].DateEntered
FROM [Bookings]
WHERE [Bookings].ConfirmationSent Is Null
ORDER BY [Bookings].DateEntered),
(SELECT TOP 1 [Bookings].DateEntered
FROM [Bookings]
WHERE [Bookings].ConfirmationSent Is Null
ORDER BY [Bookings].DateEntered DESC)
);
The above doesent work and gives 'Reserved Error (-3025)'
Am I barking up the wrong tree with this or have I just made a small error?
I have an empty database, copies of which will be used in a number of locations. Before a copy is sent out to the user some data, specific to the user's location, needs to be entered into a total of 40+ tables . I have tried to do this using a series of Update and Insert queries but find that not all of the changes have been successful.
It seems as if a block of several tables is missed every so often through the list, as if Access has had trouble doing the writes quickly enough and skipped some.
I have looked for some method of forcing each query to complete its writes before moving on to the next one, but have found nothing so far.
The database is self-contained and will be run on a PC, not on a server.
What I am looking for I guess is the Access equivalent to the Commit command used in other systems.
Will someone please tell me, is there anything like this In Access 2000?
I am trying to insert multiple rows to a table using the query
insert into rvp (rvp, rvp_name, controller_id)
values (1200, 'rvp1', 10), (1201, 'rvp2', 10)
But I am getting an error "Missing semicolon( at the end of SQL statement" and placing a semicolon at the end isn't helping either. I can insert a single row so column type or primary key isn't a problem.
Here's the table structure,
rvp - number (pk)
rvp_name - text
controller_id - number
Can someone please help me out.
Hello
Here's the problem:
I'd like to print multiple reports using a button in a form.
I guess I could use something like that:
DoCmd.OpenReport "report1", acNormal
DoCmd.OpenReport "report2", acNormal
DoCmd.OpenReport "report3", acNormal
DoCmd.OpenReport "report4", acNormal
etc
in the command.click sub but here's the tricky section:
Some of my reports (they are all based in select queries) do not contain data.
How can I make access print only these forms that include records ?
For example if the query that provides records to the "report2" returns nothing, I would like on the commabd click event mentioned above, only the report1,3and 4 to be printed. Am I asking too much?Hope you guys understand what I'm talking about. Sorry , if my English isn't too good
Thank you in advance
This is probably in the wrong forum, but I'm not sure what area the answer would cover, either queries, macros, VBA etc.
I have a form, on which is a listbox with multiple selection enabled getting it's data from a query. What I want to do is for the user to be able to select multiple products from the listbox and have some VBA code or query concatenate each id and insert them into a table, separated by commas so I can separate them again for reports etc.
Eg.
Listbox:
ID
1
2
3
User selects 1 and 2 and clicks submit. Selections are concatenated to 1,2 and inserted into the table.
Please bear in mind I'm not the best at this kind of advanced databasing, so a simple or at least easy to follow answer would be very appreciated.
Thank you all in advance.
Steve
Hi All
I have a list box. Below it lies a text box.
I'd like to click on multiple values in the list box which then populates a text box with each value to form a sentence.
currently my code looks like this
Dim Comment As String
Comment = Me.lstComments.Column(0)
Me.txtComment = Comment
Using the above method only inserts one value. How can I modify this to insert multiple values into the text box?
Much appreciated
I have 2 table and the relation between the 2 table are [Booking_Num]
Code:
table A: [ID],[Booking_Num] [Prod_Name],[Qty_Order]
table B: [ID],[Booking_Num],[Prod_Name],[Date_Out]
when I get insert to table A, should be the table B get many record base on [Qty_Order]..?
sample:
Code:
table A
ID | Booking_Num | Prod_Name | Qty_Order |
01 | BOK001 | Hammer | 4 |
table B
ID | Booking_Num | Prod_Name | Date_Out |
01 | BOK001 | Hammer | Null |
02 | BOK001 | Hammer | Null |
03 | BOK001 | Hammer | Null |
04 | BOK001 | Hammer | Null |
I am creating a table in access 2010 for my consumable and bench stock report. I made a 12 fields which I name it the month of the year and another 1 field to add the total disburse materials in one whole year. I did this formula to add the 12 fields
[Jan]+[Feb]+[Mar]+[Apr]+[May]+[Jun]+[Jul]+[Aug]+[Sept]+[Oct]+[Nov]+[Dec].
But the problem is its just adding the complete consecutive months that I disburse and the row with blank section the total disburse doesn't show on the total disburse for the whole year. I try to use the code =Nz([Total Disburse],0) but it shows on the screen i cannot be used in calculated column.
I have recently encountered some issues with my database. When I load data from an .xlsm file to my front end table, I get additional records with no data only the ID. The data I loaded comes in fine, however I get many of these records with no data only the ID. This only happens in one of my tables.
View 2 Replies View Relatedthank you in advance for all your BIG help. :)
I have 3 tables - SMaster, SI_map, ILookup
I need to create a form to Add/Update into SMaster and SI_map tables, which has one-to-many relationship, that's why I use a list box to show the values from SI_map.
in SMaster [ Sid, other fields ]
in SI_map [ primary key, Sid, ILookupid ]
in ILookup [ ILookupid, IName ]
questions:
1. How to get the multi-selected values from the list box?
2. insert these multiple records into SI_map table at the same time with SMaster (if I use the same form to populate all these fields)
3. how to populate the records into form for user to see and update the values?
Any suggestion is appreciated, it is very flexible to change any format such as SMaster and SI_map can be separate forms, as long as it works.
Thank you.
Look at the below SQL 'INSERT INTO' statement ? I'm trying to insert multiple variable values into an 'INSERT INTO' statement. I'm getting the below error message. The code is listed below. I started out with two (2) variables, but will have thirteen to insert into a table. Also, in the code below is the VBA statement to retrieve the variable data. I'm getting the data, but cannot insert the data into the table.
Private Sub Test2_Click()
Dim strSQL As String
Dim strSalesman As String
Dim strContentArea As String
DoCmd.SetWarnings False
[Code] ....
Error
Microsoft Visual Basic popup
Run-time error '3061'
Too few parameters. Expected 1.
When I import an excel spreadsheet into my Access database it always creates a couple of new blank records. I import this spreadsheet frequently so I would like to set up a macro that will automatically delete any blank records it creates. Can someone tell me specifically how to do this?
Thanks.:)