I have a table with 256 lottery drawings, pick 5, along with the drawing date. **I want to know how many times my favorite numbers (4,7,47) or any numbers, came out at the same drawing.** I am thinking

select * from lotto where
ball1 = 4, or ball2 = 4, or ball3 = 4, or ball4 = 4, or ball5 = 4
and
ball1 = 7, or ball2 = 7, or ball3 = 7, or ball4 = 7, or ball5 = 7
and
ball1 = 47, or ball2 = 47, or ball3 = 47, or ball4 = 47, or ball5 = 47

But I am not sure if this is the correct approach. I also want to do this with any 3 numbers, or hell, find which 3 numbers have appeared together, from all combinations of # of total balls. Thanks for your time. EDIT: I have found the solution for any 3 given numbers.

SELECT lotto.ball1, lotto.ball2, lotto.ball3, lotto.ball4, lotto.ball5
FROM lotto
WHERE
(
((lotto.ball1)=4) OR ((lotto.ball2)=4) OR ((lotto.ball3)=4) OR ((lotto.ball4)=4) OR ((lotto.ball5)=4)
)
AND
(
((lotto.ball1)=7) OR ((lotto.ball2)=7) OR ((lotto.ball3)=7) OR ((lotto.ball4)=7) OR ((lotto.ball5)=7)
)
AND
(
((lotto.ball1)=47) OR ((lotto.ball2)=47) OR ((lotto.ball3)=47) OR ((lotto.ball4)=47) OR ((lotto.ball5)=47)
)
;

Now, I wonder how to analyze the database to show ALL drawings where ANY permutation of 3 numbers came out together. Off to google I suppose. I will update this question if I find an answer -- or I will "answer" my own question.

Many people in my office use Oracle tables by linking them in Access (either 2010 or 2016) databases. They always complain about the fact that, when Access is about to present them the list of tables they have access to in a specific Oracle instance for selection, they have to wait 10 to 15 minutes for the selection window to pop-up. The reason seems to be the huge number of accessible tables, in turn related to the huge number of accessible schemas, on that particular instance, so that the resulting list contains tens of thousands of table names. There was a similar problem with some DB2 databases, but was solved by adding a 'SCHEMALIST' option in the ODBC configuration (db2cli.ini) for those databases, to limit the number of schemas visible to the connection. I had a look at Oracle ODBC documentation, but couldn't find a similar option, so I'm stuck. Does somebody have a trick to share about how to filter schemas/tables visible to an ODBC connection to Oracle or otherwise speed-up linking those tables?

I'm trying to create a database for work purpose, and I need you guys to help me. I don't know if my idea possible or not but i want to give a chance to apply it. As the title I want to make database for all policies and procedures in our organization. The purposes of this database are whenever my manager want to review any policy he can search for the policy by keyword, or title, or the code of each policy, also in order to make any change on that policy, he can review, edit the policy and print it. While he doing some changing on a specific policy I want to keep the original policy as well, so if I want to make a historical report for that policy I can generate all changes happens on that policy, the original one, the first change, the second change and so on. What I want to is creating a " Master Table " Consist of all updated Policies for " 4 Departments " and I want to create a separate tables for each department. And then create a relationship between all those table, (all of them link with the Policy code column -if possible-). So, whenever the user enter data from the displayed form, this data will be record in master table, and the entered data will be saved in the related department table ( if he enter a data related to the department 1, the data will be saved in the master table and the department 1 table, and etc. ). So the result of all of these are whenever I want to make a report for all entered policies I can generate it from the master table, and whenever I want to make a separate report for each policy I can do that too. My question is, if this database possible to be create, how i can create it, where I faced a lot of problems while I tried to create it, and if it's not possible, What should I do in order to get that features that my manager want? Waiting your response. The Features that I want to be exist are: 1- Display the policy in proper formatting (Making a Report) 2- Editing an existing policy. 3- Adding New Policy. 4- Make a historical report for each policy. 5- Searching bar to be search by keyword, title, policy code, and column name 6- Printing that policy 7- all document should be different by the unique version number.

I have several clients running access 32 bit with a postgres database backends. A while ago, almost all the access apps started to return "write conflicts" when the end users would make changes to the records. Something intresting we also noticed was the XMIN value for all the records that could not be changed was at (2^32)/2-1 or 2,147,483,647. However, when selected in psql, the values were different. (higher than (2^32)/2-1). I am wondering what recourse I have to fix this issue so the applications will work again.

I am a total beginner to Microsoft Access 2010. I need a form that allows a user add items from as list to a list. The items can be added until initial list is exhausted. Here's the most basic example I can give. Lets suppose that two persons attend a car show. As they are leaving, they are asked their name, and what cars they saw. First person saw only two cars. The second one saw fifty cars. On the form, they would write their name one time (at the top of the page) and then they have to fill the list of cars they saw from the list of all cars. They would not write their name next to each car. How do I create an equivalent MS Access 2010 form?

for a project I am working with a table of zip-codes that I imported from a *.csv file, exported form Excel. Those zipcodes all have 5 numbers and are formatted as text within my database. Some of them should start with 0, however during the export/import process this 0 got lost, leaving me with zip-codes that only have 4 instead of 5 digits where the leading 0 went missing. Is there a way an SQL command might add this zero to strings only having 4 digits? Thank you

I ask this question while I could not find any similar question neither on Google nor DBA Stackexchange, this question might be duplicated.
I wrote this query in MS access to check for first name and surname. But every time I run the query, I see two little input boxes which come after each other. I want to change my query so that I enter first name and surname in One/Single input box.
How can I design something like this?

Here is what I achieved up until this point:

SELECT tblGuests.firstname, tblGuests.surname
FROM tblGuests
WHERE (((tblGuests.firstname) Like "*" & [Enter Firstname] & "*") OR ((tblGuests.surname) Like "*" & [Enter Surname] & "*"));

I started a small business and have created an accounting DB with advice from my accountant. In Part of my business I generate invoices for the same customers every month. I use a unique invoice number that helps with tracking etc. So I haven't been able to get an auto number to work. Currently, I dump the records in monthly then manually update the invoice number, but it is taking too much time now. I have been trying to adapt some code I found online but I am new to the coding end of Access. Most of the time I have been able to get things done with queries and macros. So this is the code that I am trying to adapt. Private Sub InvUDB_Click() Dim InvNo As DAO.Database Dim rstMInvoice As DAO.Recordset Dim rstInv1 As DAO.Recordset Set InvNo = CurrentDb Set rstMInvoice = InvNo.OpenRecordset("MInvoice") '**** This is the line that the debugger opens up to after the error Set rstInv1 = InvNo.OpenRecordset("Inv1") rstMInvoice.MoveFirst Do Until rstInvoice.EOF If rstMInvoice!Invoice = 0 Then rstMInvoice.Edit rstMInvoice!Invoice = rstInv1!Inv rstMInvoice.Update End If rstMInvoice.MoveNext DoCmd.SetWarnings False DoCmd.OpenQuery "Invoice_Q000" DoCmd.OpenQuery "Invoice_Q002" DoCmd.SetWarnings True Loop End Sub I get an error message 3167: > “You referred to a record that you deleted or that another user in a multiuser environment deleted. Move to another record, and then try the operation again.” When I Debug it highlights The following row: > Set rstMInvoice = InvNo.OpenRecordset("MInvoice") I use the two queries below this line to generate a temp table with the new invoice number, (Max Inv No for current year and Month Plus 1). I have tried running these queries in different places in the code with no luck. As you can see I do not have a clue as how to write this or how to get this code to run. When I replace everything past the equal sign with a constant it works but it replaces all the qualifying records with that constant. Can someone help me with this?

I am making the database design of my website in Microsoft Access 2013 because it has a nice user interface and I believe it will help me understand everything a whole lot better with the UI. I have to create a relationship between two fields in one table with one field in the other - have I done this correctly (please see the screen capture below)? If not, what would I do to correct my errors? Thanks in advance.

I have a MS SQL database which is currently up and running on my Mac within a Docker container that is hosting the Ubuntu Linux version of MS SQL Server. I need to export the database to a format that can be imported into an Access (yes, *Access*) database. Alternatively, I would need to export all 300+ tables to CSV, and hopefully would not need to do them one by one. Is there any way to export all tables en masse to CSV? But this is an alternative option. I still prefer the Access-compatible export since our new SIS vendor apparently uses it. Thanks!

I am REALLY very new to this so please excuse my ignorance, but I could really use some help! I've got two tables: BillsFiled and Authors On BillsFiled, there are the columns: ID, Symbol, Filedate, Type, Author, Title, Summary, among others This is populated from an imported Excel file which is filled in by an outside source which cares not for my needs of referential integrity which means that the "Author" field can contain more than one entry, such as: ID-Symbol-------Filedate------Type------------Author--------------------Title--------------------Summary 1 - s001 ---- 11/18/2014 --- Resolution -- Smith ; Stevens ----- A Resolution to... ------- yadayada 2 - h002 ---- 11/18/2014 --- Bill ----------- Diaz -------------------- A Bill to... ----------------- yadayada 3 - s002 ---- 11/18/2014 ---- Bill --------- Ryan ; Smith ; Harris-- A bill... -------------------- yadayada Because I need a record linking "Author" to "Symbol" I also have the table: Authors. This just contains the columns: Symbol and Author. It is my most fervent desire to run a query or some vba or a macro or something to split the multiple entries in "BillFiled" to append "Authors" such as: Symbol - Author s001 Smith s001 Stevens h002 Diaz s002 Ryan s002 Smith s002 Harris I found a string of code from about a year ago offered on this site in response to a very similar question from another user and have tried using it, but am getting strange results. The code (updated with my table and column names) was: Sub SplitIt() Dim rstBillFiled As Recordset Dim rstAuthors As Recordset Dim Items() As String ' use dbOpenSnapshot to open the source table READ-ONLY Set rstBillFiled = CurrentDb.OpenRecordset( _ "SELECT Symbol, Author FROM BillFiled;" _ , dbOpenSnapshot) ' use dbOpenDynaset to open the destination table READ-WRITE Set rstAuthors = CurrentDb.OpenRecordset( _ "SELECT Symbol, Author FROM BillFiled;" _ , dbOpenDynaset) With rstBillFiled ' .BOF is Beginning of the table ' .EOF is End of the table ' Checking if both are false means there are records in the ' source table If Not (.BOF And .EOF) Then ' get the first record from the source table .MoveFirst Do ' if Author is NULL (empty) If Nz(!Author, "") = "" Then ' add a new record into the destination table ' with data from the source table for Symbol rstAuthors.AddNew rstAuthors!Symbol = rstBillFiled!Symbol ' set Author to NULL (empty) rstAuthors!Author = Null ' save the new record rstAuthors.Update Else ' if Author IS NOT NULL ' convert Author into an array of strings Items = Split(Nz(!Author, ""), ";") ' loop through the array of strings For a = LBound(Items) To UBound(Items) rstAuthors.AddNew rstAuthors!Symbol = rstBillFiled!Symbol ' Author is set to the current item in the array rstAuthors!Author = Items(a) rstAuthors.Update Next a End If ' load the next record from the source table .MoveNext ' repeat until the end of the source table is reached Loop Until .EOF End If ' close the source table rstBillFiled.Close End With ' close the destination table rstAuthors.Close End Sub With the instruction to then run SplitIt from the Immediate window. When I do that, it does collect and split the records but all of them are added into "BillFiled" rather than "Authors" Also it repeats the action with the first two records such that if there are 4 records in the source table attributable to 6 authors in total, it will add 8 new records to the source table and nothing to the destination table. As I said in the beginning, I really don't know what I'm doing and don't have any experience with code, but am trying to build this database to help a family member with their business who doesn't have time to learn how to. I have some time but sadly no knowledge so if anyone out there could take pity on me and lend your experienced eye to this, I would be so grateful! Thanks!

I am migrating a full framework .NET app to core. It used ADOX to create an MS Access database and tables. It used OLE DB to populate data. In ADOX, the data type was ADOX.DataTypeEnum.adInteger and the syntax from c# was: fld.Properties["Autoincrement"].Value = true; fld.Properties["Seed"].Value = 1; fld.Properties["Increment"].Value = 1; For memo, ADOX data type was ADOX.DataTypeEnum.adLongVarWChar. Since we can no longer use ADOX under .NET Core, I have to use SQL DML through ODBC. When I attempt to create a table using syntax similar to create table t ( col1 int not null identity(1,1), col2 varchar(512) ) I get a syntax error. When I try to adjust this for Access according to this and other related articles in the same documentation tree, I also get syntax error. create table t ( col1 counter, col2 text ) What is the correct syntax for these columns that is compatible with .NET Core and MS Access ODBC driver?

I am trying to build a query from a table (Tbl_XYZ) that will give me the information for the primary key all in one row rather then creating dups for that primary key and showing up with multiple rows. Example below.( this is just a sample) | Account Number(PK) | System_Name | Table_Name| |:---- |:------: | -----: | | 012345 | DW | Customer | | 012345 | DW | Sales | | 012345 | DW | Address | | 012345 | AWS | aws_Cust | | 012345 | AWS | aws_product | I was thinking about creating 2 separate tables for System_Name and Table_Name but it would still give me values in different rows since the table names are different. My expectation : ( Of course, I will have to create separate queries for different system names (DW and AWS) | Account Number | System_Name | Table_Name| |:---- |:------: | -----: | | 012345 | DW | Customer / Sales / Address | | 012345 | AWS | aws_Cust / aws_product | Any help with this would be greatly appreciated. I am using MS Access 2013 Thanks.

I have an access database with my work that I am trying to insert into but I keep getting. > 'You cannot add or change a record because a related record is required in table 'Projects'.' I'm running this query: INSERT INTO Tasks (Assigned,Project,Description) VALUES (@assign,@project,@description) On this Structure: With this code in C# with an OleDb... commands and connections Which are working fine for other query's:

//the assign id is already known and is of type integer.
            string query = "SELECT Project_ID FROM Projects WHERE Project_Name = @project";
            OleDbConnection con = new OleDbConnection(con_string);
            OleDbCommand cmd = new OleDbCommand(query, con);
            cmd.Parameters.AddWithValue("@project", projectComboBox.Text);
            con.Open();
            OleDbDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                project_id = Convert.ToInt16(reader);
                Console.WriteLine(project_id);
            }
                
            con.Close();

            Console.WriteLine("submit: " + project_id + " " + employee_id + " " + descriptionTextBox.Text + " " + monthCalendar1.SelectionStart.ToString("MM/DD/YYYY"));
            Console.WriteLine(monthCalendar1.SelectionStart);
            query = "INSERT INTO Tasks (Assigned,Project,Description) VALUES (@assign,@project,@description)";
            con = new OleDbConnection(con_string);
            cmd = new OleDbCommand(query, con);
            cmd.Parameters.AddWithValue("@project", project_id);
            cmd.Parameters.AddWithValue("@assign", employee_id);
            cmd.Parameters.AddWithValue("@description", descriptionTextBox.Text.ToString());
            //cmd.Parameters.AddWithValue("@deadline", monthCalendar1.SelectionStart);
            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();
            this.Close();

I have tried looking at other examples of this problem and I don't understand why I'm getting this error. @project has a valid id number of the primary key for a Project, @assign has a valid employee id as well and @description is string of text. Thanks for any help.

I am attempting to make a report in Access that uses parameters as well as a pass-through query. I'm open to using a temporary table but the guides I can find using temporary tables all use an Insert query which doesn't work with my pass-through query. I see a lot of mention of creating a recordset from the pass-though query and then copying the recordset to a temporary table but have not had luck location instructions on how to do that. Some background. I have a PostgreSQL server that is aggregating multiple databases via a foreign data wrapper. Across those databases I have multiple table names that a reused. I can do a normal pass-through query using the front end query properties but this doesn't handle parameters for the pass-through query. I can build a form using ADO objects by setting the record set for the form to equal a record set created from a pass-through query. (Set Me.Recordset = MyRecordset) This allows me to create the query string dynamically so I can use parameters! One step closer but not a report. Reports do not support setting themselves to a record set the way forms do so I'm back to the drawing board. Any suggestion on how to accomplish my goal? Should I give up and have Postgres store the results in a table, link that table, and call it a day?

I'm new to Microsoft Access so my terminolgy and understanding are limited, please bear with me. I am interfacing a piece of lab equipment via Serial/ASTM/ODBC to MS Access for Office 365 MSO 16.0.12.x. The equipment and ASTM transmit different parameters per line via the same field names. So I end up with 23 different rows in Access and each row has a different parameter and result. I use Table1 as a queue. I need to move the captured data from multiple rows in Table1 to multiple columns in a single row in Table2. I've created a greatly simplifed version of the database to try to understand how to do this properly. Table1 is a simplified example of captured data. Table2 is a simulated example of what I am trying to achieve. Any help would be greatly appreciated.

I have a large table: +----+-----------+------------+--------+--------+--------+ | Id | lClientId | datAccess | lProp1 | lProp2 | lProp3 | +----+-----------+------------+--------+--------+--------+ | 0 | 5 | 10/10/2020 | | | 9 | | 1 | 5 | 10/11/2020 | 2 | | | | 2 | 5 | 10/12/2020 | 2 | | | | 3 | 5 | 10/12/2020 | 3 | 10 | | | 4 | 6 | 10/13/2020 | 3 | 10 | 8 | | 5 | 6 | 10/29/2020 | | 11 | | | 6 | 6 | 10/15/2020 | 9 | 13 | | | 7 | 6 | 10/16/2020 | | | 16 | +----+-----------+------------+--------+--------+--------+ I need to extract each prop for each clients (lProp1 - lProp3 fields) accociated with the last date with the last date of access (datAccess can be repeated, in this case i need the last one (by Id)). I.e. the desired result is: +-----------+------------+--------+--------+--------+ | lClientId | datAccess | lProp1 | lProp2 | lProp3 | +-----------+------------+--------+--------+--------+ | 5 | 10/12/2020 | 3 | 10 | 9 | | 6 | 10/29/2020 | 9 | 11 | 16 | +-----------+------------+--------+--------+--------+ I tried to create several subqueries with SELECT which selects the maximum of each column with corresponding maximum date but the performance is falling down. Is there a way to select desiried values by single query? Because this is very expensive to go thru all the rows in table each time it search maximum value of date. The single pass is enough to get desiried values. I use ADODB and VBA so maybe it's better to go thru all the rows and extract desiried fields manually? Using Recordset.MoveNext i can check all the fields by using single pass. Just i thought the engine is faster than if i access each field by Recordeset/Variant/etc. The database is MS ACCESS. Thanks in advance.

I'm trying to use Microsoft Access to start an employee database. However, I want the display of the Employee ID to be the initials and then their number. For example, John Smith with the ID 0002 is JS0002. I've been playing around with format but it doesn't seem I can do this if I put my EmployeeID as an autonumber. Thanks in advance!

I have an MS Access database with four columns of interest that I am trying to work with. The rows represent sold jobs, and the columns of interest are "OriginalSaleAmount", "OriginalSaleDate", "RevisionSaleAmount", and "RevisionSaleDate." You can see that there is a relationship between the date and sale columns, but of course Access is not inherently aware of this. I would like to have either a report, or a view of some kind, which has a row for each month of the year, and which shows the combined sum of "OriginalSalesAmount" and "RevisionSaleAmount" which pertains to that month of the year. Of course, the years themselves should be distinguished also. We don't want data for January 2021 and January 2022 to be summed together; each should be its own year. **Here is an example to help clarify:** Let's say I have this table. |JobNo | OrigSaleAmnt | OrigSaleDt | RevSaleAmnt | RevSaleDt | |-----: | ------------:|:----------:| -----------:|:----------:| | 12345 | $98765 | 01/05/2022 | $506 | 01/29/2022 | | 12346 | $12345 | 01/24/2022 | $1028 | 02/27/2022 | | 12347 | $13579 | 02/07/2022 | $943 | 03/12/2022 | | 12348 | $12358 | 03/16/2022 | $729 | 03/19/2022 | | 12349 | $17935 | 03/29/2022 | $6821 | 04/25/2022 | I need a table, query, or report that produces this information from that table. | DateYear| DateMonth | TotalSales | Comment | | -------:|:---------:| ----------:|:----------------------------- | | 2022 | January | $111616 | $98765 + $12345 + $506 | | 2022 | February | $14607 | $13579 + $1028 | | 2022 | March | $31965 | $12358 + $17935 + $943 + $729 | | 2022 | April | $6821 | $6821 | You'll notice that each month sums the sale amount that corresponds to the date that is associated with a particular sales column. I'm not a strong Access user and I'm not entirely sure where to start with this sort of data handling. If anyone can help guide me in how to go about accomplishing this sort of thing, I'd appreciate the help.

I have just created a many to many relationship in MS Access with three tables as shown below AE Career Fair - Struc contains a list of companies where the company name is distinct. Office Locations - Int is a junction table containing in one column all the companies occuring in AE Career Fair - Struc, but duplicated based on the countries in which they occur. Countries contains a list of all the countries in the world. I was able to run a query for companies occuring in one particular country of interest. I would like to take this a step further and query this relationship and determine which companies are in more than one country. I cant really think of a way to do this based on how I currently have it set up. Could anyone provide indications as to how this would be done? Thanks.