General :: Records Using A Select Query That Is Sending To A Make Table
Feb 25, 2015
I have records using a select query that I am sending to a make table. I would like to have those records excluded from being used again for 180 days, at which point they can be used again. Essentially, I have an ID and an email address which gets stored in the Make Table. I would need to ensure that if we send an email out in Week 1, we do not send an email again for another 180 days if there is activity from that same ID. On day 181, the ID/email address can then be resent.
counting in access on a make table query.I work for a recruitment company, and have a table for the vacancies we raise. I then have a table linking to this where you can record the candidates submitted for each role. The 'CVSubmitted' and '1stInterview' are a check box yes/no. I now want to run a query where I have a sum of the total number of CV's and 1st Interviews for each role.
I am trying to make a crosstab query to filter my records from my table.
Here is the scenario.
I want to make a query that will return me my Rep ID, Rep Name, his Bonus and his GV-Q (another value) based on every month.
Now I make a crosstab query and here is the syntax.
Code: TRANSFORM First([TBL Qualification Data India].[Bonus Rank]) AS [FirstOfBonus Rank] SELECT [TBL Qualification Data India].[Rep #], [TBL Qualification Data India].[Rep Name] FROM [TBL Qualification Data India] GROUP BY [TBL Qualification Data India].[Rep #], [TBL Qualification Data India].[Rep Name] PIVOT [TBL Qualification Data India].Period;
This resulted in a column for Rep Number, one column for Rep Name and columns for all the period of Bonus I am going to have., so there are basically 9 columns for this till this month for each month and bonus value shows as values for all these month (period) columns.
Now in this same syntax, I want to have my Rep GV-Q value as well as his bonus to show in the same query, I read and came to know that it's not possible to directly have two values or two column headings in a crosstab query, I must have to make a new crosstab query and then use a normal select query to display records from these two crosstab queries, so I went ahead and made a new similar but with one value field changed crosstab query and here is the syntax for that.
Code:
TRANSFORM First([TBL Qualification Data India].[GV-Q]) AS [FirstOfGV-Q] SELECT [TBL Qualification Data India].[Rep #], [TBL Qualification Data India].[Rep Name] FROM [TBL Qualification Data India] GROUP BY [TBL Qualification Data India].[Rep #], [TBL Qualification Data India].[Rep Name] PIVOT [TBL Qualification Data India].Period;
Now after this how to make a select query to show the data from these two queries.
I can make a normal query based on these two crosstab queries and manually add all fields and then I would have my result but then after every month I have to manually enter these two extra month details from both crosstab queries to my final query and that's not what I want.
Is there any method to do this by gathering data from these two queries into one and achieve the result I want or if there is any other approach to tackle this.
To explain my database and my need for output, I am attaching few pics to make things easier if I made some mistakes in explaining my problem. It's included in attached zip since I am not able to post images or links.
While I am not new to Access, I am not well versed in its abilities as far as combo boxes go. What I have is a Form where a combo box allows you to pick from a table records 'record ID #' in order to fill in the data of that record to the rest of the form.What I want to do is use a query to select specific records from this table and allow the combo box to show only these 'selected Record IDs' for user selection.
How can I get a Query Criteria To Select All Records or specific records in query design section.
I have a table that shows many departments with credit card transactions. I like to run a query to see specific department, or have an option to see all the departments when the query is run.
I am creating a data base to handle access requests to a building. All has gone well so far and ive built tables, reports, forms and used queries. However now im trying to get abit more clever and ive hit a bump of understanding/apprach.
Whilst a ninja in Excel, im still working out which is the pointy end in Access.
The database holds all details of access requests inc: Company attending, Individual attending, Access Levels and Period of attendance. This is all done with forms for the users and functions beautifully (ish).
I can run reports on this data, based on queries (there is much more included than above but you get the idea) and generate all the reports I need.
What I wanted to do was add, following attendance to the site, the card details of the AC card they were given for the visit.
My intention was to have a form with three variables: a combobox that would let you select the individuals company and two text boxes to select a date range in. Leaving just say three or four people from that company on that day rather than everyone who had ever atteneded to sort through and add the card details.
The combo box comes from another query that gave me individual company names from the main table.
I thought a date query (as in placing a more than <> or less than criteria vs textbox value on form in the query build section) could be added but I hadnt got that far.
It seems what I have done works backwards (oops), I can adjust the query from the form but get every record in the table on the form to click through to add card details, which will be abit rubbish when i have 1000's of requests building up in the history.
I have an existing database being used for for order processing (normalized, working). The order table houses the general order info and a separate detail table holds 1 or more orderdetail records for each order in the order table, related by orderID.
A user has asked that I find a way to import her data into the system from an excel spreadsheet. She has individual columns for each type of order detail record, so for each "order" row, there may be 1 or more columns of "orderdetail"s that I will need to parse into the correct tables.
I imported the raw excel into a table, but I need to append that data into the order and orderdetail table rows (i.e. I have to create the order and orderdetail records that match every other record in the system).
It looks like I need to somehow perform a looped INSERT INTO [ORDER] (field1, field2....) VALUE (val1, val2) but there are those related detail records to contend with (the orderdetail table entries) which may be more than one insert....
I have a question about the fast food project for the restaurant, how to make the structure of the table ! also they want to working by the touch screen , if the database i created by Access and SQL it is worked on it ?
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”
i am working on a school database, in data base i have create two tables tblAccounts and tblTransaction and a form frmTransaction .
tblAccounts contain two fields GLcodes Description and frmTransaction contain Glcode transaction type debit credit date narratives
in form when i enter a glcode, lookup field match the code from tblaccounts and shows the description in form against gl code.But i am facing a problem when i enter a wrong gl code my form accept it and move to the next field and when i leave blank field of glcode same problem that i am facing, i want that , when i enter a wrong glcode in a form amsgbox will apear that asking for correct glcode.
How to I combine 2 tables with the same primary key to make one table? I have seen several thigns but none have worked. I seen inner and outer joins. All 4 of my tables have the same primary key. I just want to combine all the tables to make one table. I have a form with subforms but if I can combine all the tables and work off one rather then 4 I will be happier. Every week to 2 weeks I upload new data from excel. How would I after I add the new data combine all tables to one super table?
Wondering whether it is possible to use the 'Make Table' function in conjunction with a union query. My early attempts have failed and I am relatively new to Access.
I have for some time now been fooling around with a database to keep track of several clinical trials Basicallly I have made 4 tables..2 which defines the project ( Project and visist), 1 that populates the projects ( patients) and 1 that bind the patients visits to specific dates ( appointment)
The trouble is that im Lazy :D and therfore dont want to enter the appointment data for all patients one at a time each time we get a new guniea pig since all thats diffferent is the dates he/she has to show up. Im hoping for some way to fill up my appointment table based on patientID and projectID alone.
SO this is my 4 tables... ( some unimportant fields left out)
The idea is to create a project and then add a set amount of visits to the project ( days when they have to come and get a blood sample taken) All patients involved in the project is likewise added in the Patients table and joined to a project.
Since the patients all have to come to a fixed amount of visits ( determined by the project) I was thinking I could make a form where I pick a patient from a combobox (select projectID,PatientID,PatientName) and then automatically generate a record for each visit in my Visit table where Visits.ProjectID = cboPickPatient.ProjectID and show them in a subform so I can enter the date they have to come. I hope that makes sense. ( preferably I would like to add the date also by just entering the first Visit date and the using the DaysToNextVisit to make the other visit dates or each patient, but thats a whole other problem :o )
My instinct tells me I have to do some sort of loop code but I havent figured out the details.
Can any of you guys lead me in the right direction before I loose all my hair in frustation??
This seems to me to be a pretty simple problem-making a recordset based on a projectID and the visits involved in that project but im just out of ideas:confused:
I have two tables with compatible data. Table 1 has 23 records and table 2 has 5 records. I am trying to make a "make Table". But instead of a table with a combined 28 results, I get 115.
I am trying to run a sql statement in vba in which it selects all the records in a table related to a order Id number. I then want it loop through all the records and check for a check box which is selected. if its selected then "A" variable becomes "1" and i will then run a if statement saying if variable is 1 then run this code else run code b.
The problem I am having is that if there are 4 records the code checks through all of the 4 records and keeps changing the variable so
record 1 variable is 0 record 2 var is 0 " " " 1 " " " is 0
so although the records for a given order number does have a checked box the code wont recognise that.
What I can do or what code I can add my minds gone all blank on this.
I am developing a simple database for students to register courses using Microsoft Access 2010. I have 4 tables: Student, Register, Course, and Section (1 Course can have many Sections).
Then, there is a criteria that a student can only take up to 6 courses. Hence, may I know that where and how can I include this constraint? I believe that it should be in the Macro (Before Change) of the table Register...
My relationship is: Student -> Register <- Section <- Course
I have two tables. The first one contains customer information. Primary key is custno. The second one contains customer logins, primary key is also custno.
What I want to do is to view customer information that does not have customer logins.
I have a master table located on the backend of the server with about 3 fields:
Software Version Description
I have 4 databases, one for each software on the front end accessing this master table.
So far I have in the master table a dropdown to select 1 of the 4 software's, the version number and a brief description.
One the front end I have a button that when pressed will bring up the report based on the master table.
Can I have the report just select the software that it refers to. For instance if the datebase is Sony when I click on the report it only grabs the records with Sony on it. For my next database when I open up Sharp's database it only makes a report for the Sharp records.
How would I do this? Is this something I have to code or something I can do in criteria or do I create a query?
I linked table rawdata from a database called competitor from a ODBC Database. I run a query with to make a table with a criteria where it retrieves roughly 10 columns with 719,000 rows. And it gives me this error.
(Cannot open database". It may not be a database that your application recognizes, or the file may be corrupt.
I asked the creator of the database and they said the database grows automatically and it was created with the same Access version as i am using to run the query.
Also when I do a RUN without creating a table and just selecting and displaying the data it does not give me any issues.
