Is there another way to write the following poorly performing in Access (Jet) SQL? SELECT * FROM Det left join (select * from Inv inner join Dep on Inv.SN=Dep.SN) as mass ON (Det.RecSN = mass.SelfID) and (Det.DDTime between mass.Start and mass.End) eliminating the sub-select and doing a direct join does not work, giving a JOIN expression not supported Error in the following snippet SELECT * FROM Det left join (Inv inner join Dep on Inv.SN=Dep.SN) ON (Det.RecSN = Inv.SelfID) and (Det.DDTime between Dep.Start and Dep.End) The following produces a result, but suppresses Det records that don't match up to one or the other of the inner tables SELECT * FROM Det inner join (Inv inner join Dep on Inv.SN=Dep.SN) ON (Det.RecSN = Inv.SelfID) and (Det.DDTime between Dep.Start and Dep.End) adding another set of parentheses doesn't help other than helping Access highlight the join clause that's problematic SELECT * FROM Det left join (Inv inner join Dep on Inv.SN=Dep.SN) ON ((Det.RecSN = Inv.SelfID) and (Det.DDTime between Dep.Start and Dep.End)) dropping the between clause to a where condition results in no left join characteristics (DDTime isn't between NULL and NULL) and can lead to too many entries being held in memory while other joins are being performed to this whole mess, and can make it more logically difficult to add other where conditionals. SELECT * FROM Det left join (Inv inner join Dep on Inv.SN=Dep.SN) ON Det.RecSN = Inv.SelfID WHERE Det.DDTime between Dep.Start and Dep.End Adding a clause to make left join behavior re-emerge (but only for the portion in the ON clause) makes the world slow to a crawl, I think because Access doesn't know how to handle OR clauses without essentially running a union all in the background for each of the sides of the OR clause with the rest of the query. SELECT * FROM Det left join (Inv inner join Dep on Inv.SN=Dep.SN) ON Det.RecSN = Inv.SelfID WHERE Det.DDTime between Dep.Start and Dep.End OR Dep.Start is NULL My current work-around is to use the inner join method, then have another query that queries this one ("qCombo") and compares against the Det table for entries in Det, but not in qCombo using either a left join or where not exists. But this solution is just as inefficient (if not more so) than the sub-select method at top. SELECT 'qCombo' as QueryName, * FROM Det inner join (Inv inner join Dep on Inv.SN=Dep.SN) ON (Det.RecSN = Inv.SelfID) and (Det.DDTime between Dep.Start and Dep.End) Select Det.* FROM Det LEFT JOIN qCombo ON (Det.DDTime=qCombo.DDTime) and (Det.OtherFieldsInUniqueIdx=qCombo.OtherFieldsInUniqueIdx) WHERE qCombo.QueryName IS NULL Other possibilities?

Table1 DRank | Denom Lt | 40 CPT | 91 Table2 Name | Rank | Stat | Percentage Smith | CPT | 10 | ?? John | LT | 23 | ?? I am trying to calculate a field in the query in the calculated “percentage” field. The value is based on the Stat and the Denom. Im trying to calculate using dlookup but i keep receiving the object doesnt contain automation object errors” Ive tried this: Percentage: [stat]/dlookup(“Denom”, “Table1”, “[DRank] = “ & [Rank]) If i link the ranks in the query by adding the table it works but then I can edit data in the query. Any help would be appreciated! Edit: I just need to get dlookup to work in the query. This is the current attempt: Denom: dlookup(“Denom”, “Table1”, “[DRank] = “ & [Rank]) Still displays an error that the object doesnt contain automation. Im wanting to show the denominator value (denom) in table1 within the query where the drank in table1 matches the rank in table2 in the query.

I'm currently using Microsoft Access file format "Access 2007 - 2016". What I'm trying to do is make a form with a combo box, that when a value is selected will populate a list box with results. I have a table with multiple fields, but two relevant ones specifically. The combo box has all possible names that could appear in Field A (drawn from a separate table), and the list box needs to display all values from Field B that correspond to the chosen name. Not all names from the combo box have a corresponding Field B value, and some names have multiple corresponding Field B values. So far, I've got a macro that runs after the combo box is updated. The macro uses the OpenQuery action, then the Requery action directly after. My current problem resides in this Requery action. When I run the macro, I get an error stating that "There is no field named 'List Box A' in the current record." It also gives the error number: 2109. For the Requery's Control Name, I've triple checked that it's the same as the name shown in the list box's property sheet. What am I doing wrong?

I union all the distinct columns of the INFORMATION_SCHEMA.COLUMNS views of many databases which I query with a direct query:

select TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME from xyz
UNION ALL 
select TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME from bar
UNION ALL 
select TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME from foo

My aim is to have a quick mapping between old and new columns and any other columns that have the same name, or to check for orphaned and duplicating columns. Having the distinct TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME of everything at hand and joining that over the column_name with itself, I get a full mapping of each column to any other entity you can find. Everything works fine until I also union all one table that is not a INFORMATION_SCHEMA.COLUMNS view but a mere table with its own collation on the column that has the unpivoted column_names in the rows of the field_name column. That is why I need to make the same columns by hand, as if it was an INFORMATION_SCHEMA.COLUMNS view:

SELECT DISTINCT 'abc' AS TABLE_CATALOG, 'dbo' AS TABLE_SCHEMA, 'xyz' AS TABLE_NAME, [xyz].field_name AS COLUMN_NAME
FROM xyz
ORDER BY [xyz].field_name;

Since the query puts together the output from many database catalogs, I cannot just code in TSQL in SSMS but need to do external queries from many databases and union all the output together. I take MS Access for this, but you could also reach that aim with a Console App in C# in Visual Studio. And since I have this one query (view "xyz") in there that I need to build up by hand, and since its field_name column has another collation than the COLUMN_NAME column of the INFORMATION_SCHEMA.COLUMNS view, the following error is thrown: German error message: > odbc--call failed. [Microsoft][ODBC SQL Server Driver][SQL Server]Ein > Sortierungskonflikt zwischen "SQL_Latin1_General_CP1_CI_AS" und > "Latin1_General_CI_AS" im UNION ALL-Operator, der in der 4-Spalte der > SELECT-Anweisung auftritt, kann nicht aufgelöst werden. (#451) Put in English with deepl: > odbc--call failed. [Microsoft][ODBC SQL Server Driver][SQL Server]A > collation conflict between "SQL_Latin1_General_CP1_CI_AS" and > "Latin1_General_CI_AS" in the UNION ALL operator occurring in the 4 > column of the SELECT statement cannot be resolved. (#451) I then tried it with the needed collation, see [How do I get something like the INFORMATION_SCHEMA.COLUMNS metadata view of the INFORMATION_SCHEMA.COLUMNS view itself?](https://serverfault.com/q/1155571/607547) , but with Latin1_General_CI_AS as my default collation of the database, and therefore with the right collation, this error is thrown: > The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect. I tried to change the collations in other ways before and after this, and it seems as if MS Acces does not allow changing the collation in a SELECT command. How can I get rid of this error?

I am learning to link the back end of a database in SQL Server express 2016 to the front end in Access 2016. The tables were originally in Access and then I exported them to SQL Server. Now I am attempting to link them to the front end in Access but the problem is that they are not showing up in the possible tables that I can link. The answers I found when searching this up was that I simply did not have permission, but I am on my laptop and there are no other users and I am using Windows Authentication. I am doing this using the Northwind sample database. The following shows that I only see the system tables as options when trying to link not the desired tables. This shows the settings of my data source.

The First Question is: "List the name of each trip that does not start in New Hampshire (NH)." SELECT TripName FROM Trip WHERE State!='NH'; /* works with State='NH' */ The Second Question is: "List the trip name, type, and maximum group size for all trips that have Susan Kiley as a guide." SELECT TripName, MaxGrpSize FROM Trip, TripGuides WHERE Type=Hiking, GuideNum = 'BR01'; /* also i tried WHERE Type=Hiking AND GuideNum = 'BR01'; */ I Cant Filter by only type: "Hiking" The Third Question is: List the reservation ID, customer number, customer last name, and customer first name for all trips that occur in July 2018. I Tried This:| SELECT DISTINCT LastName, FirstName, CustomerNum, ReservationID FROM Customer, Reservation WHERE TripDate = '2018-12'; /* i tried CustomerNum.Customer */ If you need more tables and more information let me know.

Simple question, but I am new to databases. I have a field called 'ActorRetired?', and in some VBA code I need to set the value of it to -1. But When I try:

Me.ActorRetired?.Value = -1

I get the error 'Compile Error: Expected: Expression' with the question mark highlighted. How do I refer to the field name without this error? So far I have tried

Me.[ActorRetired?].Value = -1
Me."ActorRetired?".Value = -1

But neither has worked. Please help!

I am new to sql and today my manager asked me to retrieve some customres informations using their customer ID so i used the following query: SELECT * FROM Customers WHERE CustID = “11023” or CustID = “11045” or CustID = “11001” or CustID = “101989”; It did work but i wonder if there is a better way to do it.

I'm trying to use Access as a front-end for an SQL Server 2017 database. The objects I'm interested in are 6 temporal tables and a view joining them. I created an ODBC user data source and linked the tables and the view. For the view, I created an instead of update trigger that updates the underlying tables, and that works just fine. Since the primary keys of the underlying tables are identity, I figured it would be easier to insert/delete directly against the underlying tables instead of via the view (I had to SET IDENTITY_INSERT ON when manipulating the view via SQL). I can add a row to one of the tables without a problem, but when I try to delete or update a row in the table, I get an error saying "Reserverat fel (-7776). Det finns inget meddelande för felet". Loosely translated that would be something like "Reserved error (-7776). There is no message for the error". I visit the world of Windows on a regular basis every 20 years or so, so I have no real clue on where to start looking for any clues on what might be the error. Any insight? Funny thing is that if I create a view that joins two of the tables (the primary key of one of the tables is also unique in the view), I can update the view, and the underlying table is also updated (no instead of trigger necessary). It is also possible to insert a new row (for delete it seems like I will need an instead of trigger). So I guess this is a workaround, but it seems rather odd that I can update the view, but not the table.

Below is some VBA code to authorize users to login into an MS Access database. I am trying to fix this code and I don’t understand what is the issue in this code snippet.

`
Private Sub cmdLogin_Click()
Dim lngUserID As Long
    lngUserID = Nz(DLookup("UserID", _
    "tblUser", "username = '" _
    & Me!cbousername _
    & "' AND Password = '" _
    & Me!txtPassword & "'"), 0)
    If lngUserID = 0 Then
        MsgBox "Username and/or password are incorrect."
        Me!txtPassword.SetFocus
    Else
        MsgBox "Registration successful!"
        OptionSetting "CurrentUserID", CStr(lngUserID)
        DoCmd.Close acForm, Me.Name
        DoCmd.OpenForm "Homepage"
    End If
      SetWebControlAsIE9
    ChangeWebControlFeature
END SUB

` The debugger in the VBE shows the following code highlighed in yellow. Can someone help me to understand what the issue is?

`
lngUserID = Nz(DLookup("UserID", _
    "tblUser", "username = '" _
    & Me!cbousername _
    & "' AND Password = '" _
    & Me!txtPassword & "'"), 0)

`

I have a small program which compacts access database using DAO COM api. The program works, except around 50% of the time hangs on exit for about 15 seconds. So I have decided to investigate and try to understand why. What I have discovered is a https connection originating from the program back to ip addresses belonging to MS. Wireshark shows a dns lookup for nexus.officeapps.live.com. If I fire Access app, I get the same behavior, dns lookup followed by connection to MS If I downgrade DAO provider from DAO.DBEngine.120 to DAO.DBEngine.36, there is no network activity. If I block the network (disable network in VM), the timeout is gone and of course no connection. I also tried to tweak Access settings. In Trust Center/Privacy Options, both checkboxes are off. Is there any programmatic or configuration way to stop access calling back home? The program should be used in corporate environment, so there is internet connection but the direct connection will be blocked. I can't switch to Jet4, some of the sample files I have don't compact with older version. Here is the program for the reference:

import os
import win32com.client

# DAO_ENGINE = "DAO.DBEngine.36"
DAO_ENGINE = "DAO.DBEngine.120"

def compact_db(db_filename):
    tmp_filename = db_filename + "-compacted"
    try:
        os.unlink(tmp_filename)
    except FileNotFoundError:
        pass
    print("compacting ", end='')
    try:
        engine = win32com.client.Dispatch(DAO_ENGINE)
        engine.CompactDatabase(db_filename, tmp_filename)
        print("done")
    finally:
        if engine is not None:
            del engine
    os.replace(tmp_filename, db_filename)

compact_db("db.mdb")

Please help me to understand, how to convert columns to rows in MS Access. I have the below table, I would like to convert as the below result table.

`
NoOf_A NoOf_B NoOf_C NoOf_D
200    300    400    500

` Result Table:

`
Type_OfCValue  Total
NoOf_A          200  
NoOf_B          300
NoOf_C          400
NoOf_D          500

`

I have a data table below, I would like to create a query to produce a result table like below, with count and percentage, using MS Access Query. I am unable to create this.

`
Name
Alex
Alex
Bob
Bob
Roger
Tim

` Below result table

`
 Name    Count Percentage
 Alex    2     33%
 Bob     2     33%
 Roger   1     16.6%
 Tim     1     16.6%

`

I have an access database with a table that is linked to an excel sheet. I also have a duplicate table with the same data linked to a postgres database. My goal is to either link these two tables in such a way where when the excel linked table gets updated so does the postgres linked table or link a single table to both excel and postgre, is this even possible? My ultimate goal is to use the excel sheet's data in a website. The excel sheet is the primary data source and all CRUD operations are done in excel, my website is simply a dashboard to display that data. I want my website to pull the data from the excel sheet when someone makes an edit and saves the file. As far as I know there's no way to do this without buying software. Since Access edits auto update a postgres database my thought process was to keep the existing links from excel to Access and then create another link from access to postgres and use postgres as the database my App would connect to.

I have an Access database which contains all the data from an open-source Japanese dictionary, JMdict. The database contains 24 linked tables, so reading it is complicated. The reason for this is that nearly every type of information relating to a single "Entry" can have more than one value. Here is the outline of the database, in terms of how the tables relate to one another: - Entry - Keyword - Keyword_Orthography - Keyword_Priority - Reading - Reading_Orthography - Reading_Priority - Reading_ReadingToKeywordRestriction - Sense - Sense_Antonym - Sense_CrossReference - Sense_Dialect - Sense_Field - Sense_Gloss - Sense_GlossType - Sense_LanguageSource - Sense_Misc - Sense_PartOfSpeech - Sense_SenseRestrictedToKeyword - Sense_SenseRestrictedToReading - Sense_Translation Every table has a one-to-many relationship with the tables below it in the hierarchy - each Entry can have multiple Keywords, Readings and Senses, every Keyword can have multiple Keyword_Orthography and Keyword_Priority values, and so on. So visualising even a single Entry seems like a complicated task. I could (probably) write a query that puts all of the information in a single table, but it will lead to massive redundancy if, for example, I'm displaying the same "Entry" five times in order to display all five of the "Sense_Misc" values which relate to it. Which, I gather, is the whole point of using a relational database instead of a spreadsheet. Which leads me to my question: In a situation like this, how do you effectively **visualise** all data across a complex database, not just one or two tables? The best I can do right now is write individual queries to display, for example, each Keyword for a single Entry, or each Reading_Orthography for a single Reading. But I don't know what the best practice would be if I want to see the whole of the database across all of the tables. What I really need is something that can neatly sum up a single "Entry" including all of the related information, and hopefully some way of summing up all of the Entries in a single table. Is there a method for concatenating values in a single query, for example? I realise my question seems broad, so if the specific question about my situation seems unanswerable, I'll stick to the more general question: what is the best practice for visualising a relational database comprising of many tables in one place?

I have a few tables in my database which contain a Foreign Key field linking to another table. The problem is, the value for that field isn't always going to be present, and so sometimes I might have to insert a null foreign key field. To elaborate: I have a table Sense_LanguageSource which lists individual etymologies of entries in a dictionary. This has the following fields: - LanguageSourceID (PK - Autonumber) - SenseFK (FK - Number) - **LanguageCode** (FK - Number) - SourceWord (Short text) - LanguageSourceType (Short text) - IsWaseieigo (Yes/No) The LanguageCode field is the problematic case. It's used to list the language which the etymology derives from, and in this case it's a foreign key linking to a LanguageCodeValue table, which contains both the ISO-639-2B code and the full name of the language. However, some of the etymologies I'm going to insert into the database don't have LanguageCodes, so I would have to insert a 0 into that field. Initially, Access wouldn't let me do this, so I disabled Referential Integrity. Then when I reopened the database, Access said that the Database was compromised and needed to be repaired. So I think that I need an alternative solution to this. I would make it less normalised and simply have a text field for the LanguageCode in there, but there are two tables that refer to the LanguageCodeValue table, and it seems to make a lot of sense to have a table describing what each LanguageCode refers to. The solutions I can think of right now are: 1. Remove the relationship between the LanguageCode fields and the LanguageCodeValue table (so that it's purely a reference table, and not linked,) and just use SELECT queries if I need to look-up the meaning of the LanguageCode. 2. Keep the link between the tables without Referential Integrity to allow null foreign keys, and hope that the database doesn't break down. 3. Insert a ""null"" value in the LanguageCodeValue table to refer to (by which I mean an actual record with a key value of 0 or similar, and blank values for the text fields), so that instead of entering a null foreign key, I can just link to a record that doesn't mean anything. 4. Another solution? As I'm new to databases and not sure about best design practices, I would like to know which solution is most appropriate in my situation.

I'm very new to Access, so I'd appreciate patience if this is a basic topic. I'm creating a lot of tables which I need to link. I have two tables, Keyword and KeywordOrthography. KeywordOrthography has a KeywordFK field that connects to the Keyword.ID field. As far as I know there are two ways to connect KeywordFK to KeywordID: 1. Go into the relationships window, drag Keyword "ID" to "KeywordFK", create a One-to-Many relationship (enforcing referential integrity.) 2. Clear any existing relationships between the two tables, go into Table Design view, and on the KeywordFK field, select Lookup Wizard. Select get values from another table, and import the ID field from Keyword. Then when I go to input a new record into KeywordOrthography, I can select from already existing Keywords to input. Both methods create a 1-to-n relationship with the table, and the second way makes it easier to input a new record manually, though I'm not sure if it causes any complications when using SQL or other methods when adding new records. On the other hand, the first method is much faster when it comes to linking tables. Question is: Are these methods equivalent as I've pre-supposed? Is there an advantage to doing it one way or the other?

Hello database administrators. I got some excel spreadsheet with data of "my customers" and I'd like to create professionally designed database out of it. Can anyone tell me how this data should be normalized, what tables should be created and how should they be related with each other, so that my database would meet corporate industry standards? I know I'm just a beginner in database creation but I've already learned about concept of database normalization, I'm eager to learn and I'll be grateful for any help and directions. Here is the first chunk of my data imported to access table. As you can see I've already changed the job's names into numbers and I have separate table where these numbers are assigned into the jobs names. Here it is in jobs table. Jobs table with jobs names. Here is the rest of the data - please help me organize it. How about this *BESTMovie columns - do they need normalization? Some movies repeat 10 times and others don't repeat at all. And one more question to this "*Profession" and "*BESTMovie" columns. If I had some person, lets say clint eastwood or robert de niro, that I'd like to assign not only 3 but 5 movies, how to do this without adding another "*BESTMovie" column, where most of the fields would be blank for some other people who didn't have more than 3 movies? I've heard about some rank column or rank table but I have no idea how should it look like. The rest of my data looks like this: Here is an "EyesColor" column and "Adjective" I use to describe a person. Can these columns be normalized and should the be normalized? The eyes color repeats many times of course, and adjectives repeat less frequently but they do repeat. I hope the question is well put and I'm looking forward for answers from you guys.

Let's say I have a transaction where Party1 and Party2 are associated, eg by a transaction - let's say Party1 sells something to Party2. I have a list of transaction IDs, eg transaction1 is Party1 sells goods to Party2, transaction2 is Party3 sells goods to Party4 and so on. I have another table, Entities, that tells me that Party1 is 'Smith Enterprises' and Party2 is 'Jones Corp' etc. I want to create a query where Access puts the name of the party in for both parties, eg the transaction now says 'Smith Enterprises' sells goods to 'Jones Corp'. I can't figure out how to get a query to look up Party1 and Party2 in Entities and insert the entity name for both. I can do them one at a time and create a list of identified sellers or a list of identified buyers, then join them back together, but I feel sure there must be a more efficient way of doing this. Apologies if this is either a trivial request or unclear; I'm a 'semi-competent amateur' trying to help myself in the absence of organisational coding resources. But if you tell me which command(s) I need to use, I can probably figure it out from tutorials.

Ok, been a good decade since I've worked with Access and SQL, so a little forgiveness please. Short version - when I add the query criteria to only display rows which match today's date, the query returns no rows; despite there indeed being matching rows there. I have a sneaking suspicion that [FG Orders by Date].[FG Date] is somehow somewhere being treated as text, because after looking at the sort results of a query, it isn't sorting the field properly regardless of sort order. Hope the following information is enough for someone to help me understand where I've gone wrong. **Problem being addressed** The query, and report coming from it, is a Kitchen Production Report that shows the Component Goods that need to be produced for the Finished Goods they have to dispatch that day. There's a complexity of nicely laid out tables which manage things nicely, which will become apparent from the SQL below. Due to the workflow, this report only ever needs to show the information for the current day. **Summary** I have a query, where the date column [FG Orders by Date].[FG Date], which is formatted as a Short Date (DD/MM/YYYY). I've tested it's correctly formatted by creating an Expression column to transform it into a different format, which works perfectly. Rows are returned by the query without an issue. Whenever I add criteria to the query to only return rows for today's date (13/06/2019) - using Date(), the query returns no rows despite there being 11 rows with that date value. Remove the criteria agaun, and everything comes back nicely. **SQL** This is the query code without the criteria added: SELECT [FG Orders by Date].FGOrderID, [FG Orders by Date].[FG Date], [FG Orders by Date].[FG Date] AS ExpTodaysDate, [FG Orders by Date].FGSKU, [Finished Goods].[FG Name], [FG to CG mapping].CGSKU, [Component Goods].[CG Category], [CG Category].[CG Category Name], [Component Goods].[CG Name], [Component Goods].[CG Unit], Units.[Unit name], [Component Goods].[CG Unit value], [FG Orders by Date].[FGSKU Quantity], [FG to CG mapping].[CG Qty Required for FGSKU], [FGSKU Quantity]*[CG Qty Required for FGSKU] AS TotalQtyOfCGSKU FROM (Units INNER JOIN ([CG Category] INNER JOIN [Component Goods] ON [CG Category].[CG Category ID] = [Component Goods].[CG Category]) ON Units.[Unit Symbol] = [Component Goods].[CG Unit]) INNER JOIN (([Finished Goods] INNER JOIN [FG Orders by Date] ON [Finished Goods].[FGSKU] = [FG Orders by Date].[FGSKU]) INNER JOIN [FG to CG mapping] ON [Finished Goods].[FGSKU] = [FG to CG mapping].[FGSKU]) ON [Component Goods].[CGSKU] = [FG to CG mapping].[CGSKU]; And with the criteria added: SELECT [FG Orders by Date].FGOrderID, [FG Orders by Date].[FG Date], [FG Orders by Date].[FG Date] AS ExpTodaysDate, [FG Orders by Date].FGSKU, [Finished Goods].[FG Name], [FG to CG mapping].CGSKU, [Component Goods].[CG Category], [CG Category].[CG Category Name], [Component Goods].[CG Name], [Component Goods].[CG Unit], Units.[Unit name], [Component Goods].[CG Unit value], [FG Orders by Date].[FGSKU Quantity], [FG to CG mapping].[CG Qty Required for FGSKU], [FGSKU Quantity]*[CG Qty Required for FGSKU] AS TotalQtyOfCGSKU FROM (Units INNER JOIN ([CG Category] INNER JOIN [Component Goods] ON [CG Category].[CG Category ID] = [Component Goods].[CG Category]) ON Units.[Unit Symbol] = [Component Goods].[CG Unit]) INNER JOIN (([Finished Goods] INNER JOIN [FG Orders by Date] ON [Finished Goods].[FGSKU] = [FG Orders by Date].[FGSKU]) INNER JOIN [FG to CG mapping] ON [Finished Goods].[FGSKU] = [FG to CG mapping].[FGSKU]) ON [Component Goods].[CGSKU] = [FG to CG mapping].[CGSKU] WHERE ((([FG Orders by Date].[FG Date])=Date())); **SQL Create statements for relevant tables** (This is based on an export using DBWScript ) CREATE TABLE [CG Category] ( [CG Category ID] AUTOINCREMENT, [CG Category Name] TEXT(255) WITH COMPRESSION, CONSTRAINT [ID] PRIMARY KEY ([CG Category ID]) ); ALTER TABLE [CG Category] ALLOW ZERO LENGTH [CG Category Name]; ALTER TABLE [CG Category] FORMAT [CG Category Name] SET "@"; CREATE TABLE [Component Goods] ( [CGSKU] DOUBLE NOT NULL, [CG Name] TEXT(255) WITH COMPRESSION, [CG Unit] TEXT(255) WITH COMPRESSION NOT NULL, [CG Unit value] DOUBLE, [CG Category] LONG, [CG Pick Location] TEXT(255) WITH COMPRESSION, CONSTRAINT [CGSKU] PRIMARY KEY ([CGSKU]) ); ALTER TABLE [Component Goods] ALLOW ZERO LENGTH [CG Name]; ALTER TABLE [Component Goods] ALLOW ZERO LENGTH [CG Unit]; ALTER TABLE [Component Goods] ALLOW ZERO LENGTH [CG Pick Location]; ALTER TABLE [Component Goods] FORMAT [CGSKU] SET "General Number"; ALTER TABLE [Component Goods] FORMAT [CG Name] SET "@"; ALTER TABLE [Component Goods] FORMAT [CG Unit value] SET "General Number"; ALTER TABLE [Component Goods] FORMAT [CG Pick Location] SET "@"; CREATE TABLE [FG Category] ( [CG Category ID] AUTOINCREMENT, [CG Category Name] TEXT(255) WITH COMPRESSION, CONSTRAINT [ID] PRIMARY KEY ([CG Category ID]) ); ALTER TABLE [FG Category] ALLOW ZERO LENGTH [CG Category Name]; ALTER TABLE [FG Category] FORMAT [CG Category Name] SET "@"; CREATE TABLE [FG Orders by Date] ( [FGOrderID] AUTOINCREMENT, [FG Date] DATETIME DEFAULT Now(), [FGSKU] DOUBLE, [FGSKU Quantity] LONG DEFAULT 0, CONSTRAINT [PrimaryKey] PRIMARY KEY ([FGOrderID]) ); ALTER TABLE [FG Orders by Date] FORMAT [FG Date] SET "Short Date"; ALTER TABLE [FG Orders by Date] DECIMAL PLACES [FGSKU Quantity] SET 0; ALTER TABLE [FG Orders by Date] FORMAT [FGSKU Quantity] SET "General Number"; CREATE TABLE [FG to CG mapping] ( [FGCG ID] AUTOINCREMENT, [FGSKU] DOUBLE, [CGSKU] DOUBLE, [CG Qty Required for FGSKU] DOUBLE, CONSTRAINT [ID] PRIMARY KEY ([FGCG ID]) ); ALTER TABLE [FG to CG mapping] FORMAT [FGSKU] SET "General Number"; ALTER TABLE [FG to CG mapping] FORMAT [CGSKU] SET "General Number"; ALTER TABLE [FG to CG mapping] FORMAT [CG Qty Required for FGSKU] SET "General Number"; CREATE TABLE [Finished Goods] ( [FGSKU] DOUBLE NOT NULL, [FG Name] TEXT(255) WITH COMPRESSION, [FG Category] LONG, [FG Category Old] TEXT(255) WITH COMPRESSION, [FG Pick Location] TEXT(255) WITH COMPRESSION, [FG Handle in System] BIT NOT NULL DEFAULT =Yes, CONSTRAINT [FGSKU] PRIMARY KEY ([FGSKU]) ); ALTER TABLE [Finished Goods] ALLOW ZERO LENGTH [FG Name]; ALTER TABLE [Finished Goods] ALLOW ZERO LENGTH [FG Category Old]; ALTER TABLE [Finished Goods] ALLOW ZERO LENGTH [FG Pick Location]; ALTER TABLE [Finished Goods] FORMAT [FGSKU] SET "General Number"; ALTER TABLE [Finished Goods] FORMAT [FG Name] SET "@"; ALTER TABLE [Finished Goods] FORMAT [FG Category Old] SET "@"; ALTER TABLE [Finished Goods] FORMAT [FG Pick Location] SET "@"; ALTER TABLE [Finished Goods] FORMAT [FG Handle in System] SET "Yes/No"; CREATE TABLE [Units] ( [Unit ID] AUTOINCREMENT, [Unit name] TEXT(255) WITH COMPRESSION, [Unit Symbol] TEXT(255) WITH COMPRESSION, CONSTRAINT [PrimaryKey] PRIMARY KEY ([Unit ID]) ); ALTER TABLE [Units] ALLOW ZERO LENGTH [Unit name]; ALTER TABLE [Units] ALLOW ZERO LENGTH [Unit Symbol]; ALTER TABLE [Units] FORMAT [Unit name] SET "@"; ALTER TABLE [Units] FORMAT [Unit Symbol] SET "@"; CREATE INDEX [CG Category] ON [Component Goods] ([CG Category]); CREATE INDEX [CG Unit] ON [Component Goods] ([CG Unit]); CREATE INDEX [FGSKU] ON [FG Orders by Date] ([FGSKU]); CREATE INDEX [FG to CG mappingCGSKU] ON [FG to CG mapping] ([CGSKU]); CREATE INDEX [FG to CG mappingFGSKU] ON [FG to CG mapping] ([FGSKU]); CREATE INDEX [FG Category] ON [Finished Goods] ([FG Category]); CREATE INDEX [Unit ID] ON [Units] ([Unit ID]); CREATE INDEX [Unit Symbol] ON [Units] ([Unit Symbol]); ALTER TABLE [Component Goods] ADD CONSTRAINT [CG CategoryComponent Goods] FOREIGN KEY NO INDEX ([CG Category]) REFERENCES [CG Category] ([CG Category ID]); ALTER TABLE [Component Goods] ADD CONSTRAINT [UnitsComponent Goods] FOREIGN KEY NO INDEX ([CG Unit]) REFERENCES [Units] ([Unit Symbol]); ALTER TABLE [FG Orders by Date] ADD CONSTRAINT [Finished GoodsFG Orders by Date] FOREIGN KEY NO INDEX ([FGSKU]) REFERENCES [Finished Goods] ([FGSKU]); ALTER TABLE [FG to CG mapping] ADD CONSTRAINT [Component GoodsFG to CG mapping] FOREIGN KEY NO INDEX ([CGSKU]) REFERENCES [Component Goods] ([CGSKU]); ALTER TABLE [FG to CG mapping] ADD CONSTRAINT [Finished GoodsFG to CG mapping] FOREIGN KEY NO INDEX ([FGSKU]) REFERENCES [Finished Goods] ([FGSKU]); ALTER TABLE [Finished Goods] ADD CONSTRAINT [FG CategoryFinished Goods] FOREIGN KEY NO INDEX ([FG Category]) REFERENCES [FG Category] ([CG Category ID]); **Example of query where date isn't sorting properly** (Apologies, i've tried posting this in markdown, but I can't figure out why the generated code isn't formatting properly)