Modules & VBA :: Generate Multiple Records In One Table From Single Record In Another Table
Sep 20, 2014
I am building a simplified re-order point system - if inventory position drops below a certain level (the yellow level is this case) one or more purchase order lines has to be created in another table.
I have one table with the following field and data:
ItemId Red Yellow Green Multiple Inventory position
0001 10 30 50 5 45
0002 5 40 47 5 23
0003 11 20 30 10 5
I would like to generate new records (in another table) based on the above fields and three records.Basically the end result should look as the following:
The logic is quite simple - if inventory position is less than the yellow value new order lines should be created in multiple qty (based on the multiple field) until the aggregated value (in table 2) is above the green value.The priotization value should be based on the start inv (in tbl 2) compared to the values in red, yellow and green in tbl 1.
I have a table with a field that contains IDs, e.g.
123 456 789
I would like to generate a single string from this table, seperated by commas, e.g. 123, 456, 789 and output to a field in an existing table. This will then be used in a SQL statement.I am new to VBA and don't reallly know where to start/
In a situation where I imported an excel file with so many columns and split them into two temp tables and they are linked using a key.
the data has a fixed part lets say
Field1....Field2.....Filed3.....Field4...then Field5.....Field6.....Field7....Field8 is the same data range as Field9...Field10...Field11...Field12. I would want to split this data into multiple rows like this
Field 1 Field2 Field3 Field4 Field5 Field6 Field7 Field8 Field 1 Field2 Field3 Field4 Field9 field10 field11 field12 and so own...
I Work for an NHS organization. A department here orders prescriptions and sends them out to Gp Surgeries. I have been tasked in designing a database to make this easier for them.
Prescriptions arrive on a Pallet. Each pallet has 100 Boxes of prescriptions on it. Each box Contains 2000 Individual prescriptions. I have a table which contains the following:
Box Number Serial Start Number Serial End Number
When the user enters the first box number and the serial start number, i need it to calculate the serial end number and insert another 99 rows into the table and also calculate the serial start and end numbers for these other 99 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.
I've got a table tblPatienten, a table tblRechnung and a form frmRechnung.
The primary key in tblPatienten is KundenNummer, the primary key in tblRechnung is RechnungsNummer. The relationship between tblPatienten and tblRechnung is one to many.
Now, every patient (stored in tblPatienten) is allowed to make multiple orders (stored in tblRechnung).
How can I assign each new order entry in frmRechnung to an existing patient in tblPatienten?
I'm trying to create a report that's based on a query, and the query has three fields: [PersonName], [PersonDate], [PersonShift]. This table holds records for people that worked on certain days and certain shifts. What I want to do is create a report that gives a graphical calendar display of each day in a month, and on any day that the person has a record (and sometimes there are more than one), I'd like to see just the PersonShift records showing in that day's box.
tblPersons PersonName PersonDate PersonShift Jason 4/10/14 FIRST Jason 4/13/14 FIRST Jason 4/13/14 SECOND
So if I were to print this report for Jason, I'd get all the days in April laid out like a calendar, and on 4/10/14 you'd see "FIRST" in the box, and on 4/13/14. you'd see FIRST and SECOND in the box. All the other boxes would be blank.how to display the calendar, how to display the dates. I'm able to return records to those boxes by creating 31 separate queries, one for each day of the month, and each query returns records for that day. The queries are added to the report as subreports. It all works beautifully.
The thing is, I'm running 31 queries every time I pull the report. Is there a way to code a single field on a report that will run a SELECT statement on the table using variables that are located in fields elsewhere in the record?
If I have a PersonName field on the header of the report, and I have a PersonDate field in the detail of the report's record, can I create a new field in the detail of that record that runs a SELECT statement on qryPersons, and filters the tblPersons by the PersonName on the header of the report and on PersonDate in the record?
I want a field on a report that runs a SELECT statement on tblPersons, I want the field to return only the PersonShift records for that person based upon the PersonDate. Each of the fields on my report have a CalendarDate field, and I want the SELECT statement to return records where the PersonDate matches the CalendarDate, and again, it should only display PersonShift records.
I have a reasonably complex query (3 subqueries into 1 main query) which gathers data from various tables into a single dataset based on a specified date.
I now need to generate a similar dataset but across a range of dates (a month) for reporting purposes. However, I can't just adapt the query and change the parameter from a "=#<Date>#" format to a "Between #<Date1># And #<Date2>#" format
The reason being, each date has to be treated individually and has to be queried as a standalone. It's to do with the type of data I have (one-to-many relationships between tables)
So what I really need to do is run the same query multiple times, for each date in scope, then stitch all of those datasets together into one 'giant' one.
How to do that in SQL (effectively, have one query produce the dates in scope, then join that onto the other query, passing each date as the parameter - I don't even think that's possible to be honest)
The other option I can think of is to use VBA to loop through the dates in scope, then use a QueryDef object to set the parameter and read the records for each date into a Recordset object. But then I have the problem of stitching all the Recordsets together, without looping through all the fields and rows each time.
I have a form that the users enter data into and send a report each day. I would like that in a click of a button all the data I entered the day before and that have ="Open" will be entered into the tables with today's date. Is that possible?
I am trying to determine the best method for how to handle this query using Access 2013. I have a clients table that contains the following:
clientID fName lName admissionDate dischargeDate 1 John Doe 05/06/2014 06/27/2014 2 Jane Doe 04/24/2014 05/15/2014 3 Steven Smith 05/15/2014 NULL/Empty 4 Chris Davis 06/12/2014 NULL/Empty
Then there is a WeeklyProgressNotes table that is there for the person that is responsible for auditing the clients charts. It does not contain the actual weeklyprogressnotes, it only contains a Yes/No field and a date field for the date the weeklyprogressnote was completed. Like below:
I am creating a form that the auditor can open to determine what weeks she needs to check for each client to see if they have their weeklyprogressnotes completed that week. The weeks run Mon - Sun and there will be no record in the WeeklyProgressNotes table if she has not yet checked and confirmed for that week. So the form would basically look like this:
fName lName week completed date clientID(hidden) John Doe 5/19/14-5/25/14 Checkbox Null 1 John Doe 5/26/14-6/1/14 Checkbox Null 1 John Doe 6/2/14-6/8/14 Checkbox Null 1 John Doe 6/9/14-6/15/14 Checkbox Null 1 John Doe 6/16/14-6/22/14 Checkbox Null 1 John Doe 6/23/14-6/29/14 Checkbox Null 1 Jane Doe 4/28/14-5/4/14 Checkbox Null 2 and so on.......
I have thought about creating an SQL statement to select all of the clients and then creating a function that determines their admission date within the specific week and their discharge date withing the specific week and then create a loop with another SQL statement with a BETWEEN clause for all the weeks and determine if there is an entry in the WeeklyProgressNotes table or not. If not then I would display out the above info. I'm not sure if there is an easier, less search intensive way of doing it. Maybe an SQL query that can cut done on some of the looping.
I have a table called bookings, three records within that table are;
arrivaldate departuredate breakfast
This Table (Bookings) is linked to the guest table via GUESTID..Assuming that a guest arrive on the 5th and leaves on the 10th, I am trying to create a breakfast voucher for the guest for each day of their stay.
So 5 vouchers, 1 each for the 6th, 7th, 8th, 9th, and 10th.I have created the breakfast voucher as a report and a query to include data but I dont know how to propagate the new data to actually create the vouchers. All I can get is data for the arrival or departure fields.
Hello friends,I have a pretty noobish question. I was wondering if it was possible to have several (3 in my case) different forms that apply data to a single table? The main table has three different types of data: 1. Client info 2. Services A 3.Services B. Each type of data has about 7/50/12 different fields of data in each set so that trying to include all this data on a single form is not very user friendly due to clutter.I have three forms that input data into the table, however the record navigation command buttons that I have on the forms do not apply the data to the correct record. For instance, when I input the Client info for client #2, and click the "open services A form" command button, the form opens on client #1 Services A instead of to a new record. Is this something I have done wrong, or is it due more to MS Access limitations? Thanks
I'm using access 2010. I want to put some code behind a button that will allow the user to add selected products in a listbox to which ever operation is selected in a combobox by creating records for each product / operation combination in a third table:
I have a form with a combobox on top that allows the user to select the operation for which they want to add products. There is also a listbox that displays all records in tblProducts. The user can select one or more products and then the idea is that they can then press a button that will use the selected record IDs from the list box and the record ID from the combo box to create new records in the many to many table.
Hi guys. I'm a bit of a noob with Access. I have a table which is 15 rows by 250,000 columns. So there are about 3.75m records.
What I need to do is to get access to export a number of csv files automatically from this table.
I need each csv file to contain all the data in the table for a certain range of rows. This is determined by a value in a particular column. This is a sample of the table.
http://i30.tinypic.com/aljf5s.jpg
Basically Store_Nbr represents a UID for a particular store. All the data is currently in one big table, and I need a single csv file for each different store_nbr (so it contains all the data in the table for each unique store). Also, for each store_nbr, there are different Year Month of Surveys. For each store_nbr, I need a different csv file for each Year Month of Survey. This will probably divide up the 250,000 long table into about 200 csv files, as there are about 50 different Store_nbrs, and there are 4 surveys for each individual store.
Sorry if I've not made myself perfectly clear.
I'm sure there's a simple way of doing it, but I really do suck with access and was wondering if you guys had any idea.
On our main menu table, I only want to be able to add one record.
On the form that I have linked to this table, should you press the tab key to go through all the items / buttons, it opens a blank record screen. However, I do not want this. Any ideas?
Here is my problem: I have an Access database that contains a table with three columns (A & B & C). Field A is for code values and field B is for the value for that particular code. Field C holds the case number. I have a folder that contains the information for over 5000 case numbers. Each case number needs to be placed inside of the table. Considering there are 5000 case numbers, I dont want to use the import wizard 5000 times.
Furthermore, the text file only has two columns (for A and B).
I am trying to create a VBA that will append rows to the table by going into each file and placing the information into the table. The first row for each file contains the case number. So when the VBA runs, it should open the text file get the case number from the first row (field B on the text file) and place that in the case number field of the table for the entire file; and then repeat this action for all of the files in the entire folder.
Any help that can be provided will be greatly appreciated.
I am having trouble creating a query where I am trying to count number of records for different fields for a particular criteria, and combine the results into a single table.
My table is in the form, TimeandDate,WS127m_Avg,WS82m_Avg....
I want to count those records where a 9999 is reported, and report by month. For a single field I can do this OK using,
SELECT DateSerial(Year([TimeandDate]),Month([TimeandDate]),1) AS [Month], Count(WS127m_Avg) AS 9999s FROM CL_AllData WHERE (((WS127m_Avg)=9999)) GROUP BY DateSerial(Year([TimeandDate]),Month([TimeandDate]),1);
I can't figure out how to report an additional field (WS82m_Avg) at the same time, checking for the same criteria in that field (i.e. WS82m_Avg = 9999).
I have some data in excel which I am importing it into MS Access 2002. Each excel file (one worksheet per file) is imported into separate tables.
I want to combine all my imported tables into one table. Is that possible, if yes then how and if no then what can be done to get single table after impoting data from various excel files.
I'm developing a simple sporting records db. I have a 'Competitor' table listing competitors as follows: CompetitorPK, Name, Division, Club. I have a 'Contest' table listing contests as follows: ContestPK, Competitor1_FK, Competitor2_FK, Winner_FK, Score etc. My question is have I modelled tables correctly i.e. CompetitorPK will appear in three columns of Contest table. How do I define this relationship? What alternative is there to what I have done.I intend to use forms to populate both tables (independantly obviously).
I tried the idea sugested in post (http://www.access-programmers.co.uk/forums/showthread.php?p=423080#post423080) but it didn't seem to give the cross referencing that I had hoped for .... example 1 is related to 4,3, and 5 while 6 is related to 1 thus implying an extended relationship to 4,3, and 5.
In a standard one-to-many I'd look at record 1 and see that it is related to 4,3,5 but if I were to look at record 6 I'd only see that it is related to 1. How would I set up the table relationships to drill further to see that 1 is also related to 4,3,5?
How do you do a many-to-many from one table back to that same table?
tblEvent EventID(AutoNumber) EventTitle(Memo) 1 memo content 2 data 3 more memo content 4 some info 5 more stuff 6 other text
I am trying to create a form with a button attached to each record that would allow the user to click the button and it would automatically open outlook and fill in the TO:, SUBJECT: and BODY: fields. Here is the code that I currently have:
Code: Private Sub Command33_Click() Dim strEmail As String Dim strMsg As String Dim oLook As Object Dim oMail As Object Set oLook = CreateObject("Outlook.Application") Set oMail = oLook.CreateItem(0)
[code]...
There are two issues I keep running into:
1. This code opens outlook and populates all of the fields but pastes the email incorrectly. Instead of pasting just the email (email@email.com) it pastes the html tags as well (email@email.com#mailto:email@email.com#) which means that the user would have to delete everything between the #'s in order to send the email every time.
2. I currently have the email BODY pulling from a table but this obviously limits what I can do. I would like to simply encode the BODY within the VBA code. The setup I am looking for is: one paragraph a blank line a hyperlink to a website a blank line another paragraph
:eek: I have a large table with 1 damn corrupted record, the database can't be repaird, compact, I can't use the copy ,method since the table is over 9000 record sets.
I tried creating a new table and appending the non-corrupted record into it with no success, it is keep locking on me.
Have you guys tried anything else that works for this situation???
