Query misfiring and giving wrong results

[unclemeat]

what is PK and FK ?


sorry if im a goose at this.

[Bob Fitz]

hi bob
this is still same problem i think.
if you open reports f and put in 4/4/2023 and open the ebaybookqry query (its pretty much the same as the cd one except for books) (with the new code under recordcreated) you get 283 records.
if you then close reports f and run the query, it prompts for a date and so you put in 4/4/2023 you get 9 records (which i think is right). not sure why the other records are coming in - there are heaps that were last changed on 25/02/2023, 6/03/2023 etc which are obviously not greater than 4/4/2023.
just not understanding why they are being selected by the query.
thx
rob

I agree. Same problem, and you may have that problem in other queries too. Same solution required. Change the expression in every query that references the date value on a form with the one I that I gave you. But before you do that, read a bit about table design, relationships and normalize your tables.

[Welshgasman]

hi bob
this is still same problem i think.
if you open reports f and put in 4/4/2023 and open the ebaybookqry query (its pretty much the same as the cd one except for books) (with the new code under recordcreated) you get 283 records.
if you then close reports f and run the query, it prompts for a date and so you put in 4/4/2023 you get 9 records (which i think is right). not sure why the other records are coming in - there are heaps that were last changed on 25/02/2023, 6/03/2023 etc which are obviously not greater than 4/4/2023.
just not understanding why they are being selected by the query.
thx
rob

Well I would agree with 25/02/23 as that is unambiguous, but the other would be taken as 3rd June, which woul be later?
In hospital car ark atm, so cannot look at the db.

[Welshgasman]

where would you put that code in a database ?

Is that addressed to me?

You put that constant in a module.
The you just use it Format(YourDateField,strcJetDate)

[unclemeat]

Hi bob
the problem is still happening after I put your code in the query.
thatÂ’s what I was trying to explain.
it didnÂ’t seem to weed out the wrong dates.
only when I put a date in the parameter box that pops up does it give what I think is the right results.

[unclemeat]

Is that addressed to me?

You put that constant in a module.
The you just use it Format(YourDateField,strcJetDate)

it was yes - does a module operate for the entire database ?

[Bob Fitz]

Hi bob
the problem is still happening after I put your code in the query.
thatÂ’s what I was trying to explain.
it didnÂ’t seem to weed out the wrong dates.
only when I put a date in the parameter box that pops up does it give what I think is the right results.

Can you cut and paste here a copy of what you have in the criteria row of the query

[mike60smart]

Hi

PK stands for Primary Key and all tables should have a PK - Autonumber to identify each Unique Parent record in the table.

FK stands for Foreign Key. The FK is the name of the related Child record in the related table.

You always link the PK to the FK

You would always Enforce Referential Integrity between the two related tables which ensures that you cannot add a Child Record
unless you have a Parent record.

[unclemeat]

hi Bob - getting a bit confused trying to normalise the db.
so i set up a table with all music styles in it - these are just the same yes/no right ? as in yes its a reggae cd or no its not.
for the second table what is shopifyID ? is that the Stock ID # in shopify table ? or a new field ? if its new what is it referencing ?
thx for any help
rob

The fields I indicated should be in a table called tblStyles

tblStyles
-StyleID PK
-Style
You then need to create an additional table named tblMusicStyles

tblMusicStyles
-MusicStyleID - PK
-ShopifyID - FK
-StyleID - FK (Linked to PK tblMusicStyles)

You can then add a number of related Styles

[unclemeat]

Can you cut and paste here a copy of what you have in the criteria row of the query

i was just using the database exactly as i uploaded it with the only change to the query being the code you sent.

[Bob Fitz]

i was just using the database exactly as i uploaded it with the only change to the query being the code you sent.

But what I asked you was:

Can you cut and paste here a copy of what you have in the criteria row of the query

I tried the criteria expression that I offered you before I posted it. It worked for me. I would expect it to work for you. If it does not work for you, then my first suspicion would be that you have entered it incorrectly. Sometimes, we need your help to help you. Did you read any of the link that I posted for you?

If you get this criteria expression working correctly, you will need to make the change in every query that uses your original criteria expression for them to work correctly

Have you read anything on relational database design and relationships, Primary keys, Foreign Keys Normalisation?

I you restructure your database tables most of your queries will be redundant.

[Welshgasman]

it was yes - does a module operate for the entire database ?

Yes, otherwise I would have not suggested it?

[mike60smart]

Hi

If you open frmShopify this displays the Main Form based on your "Shopify" table.
I made StockID the PK

I then created the 2 tables mentioned earlier. tblMusicStyles & tblStyles
I then set Referential Integrity between these 3 tables.
Pm the Main form I created a Subform based on "tblMusicStyles" ond then in the Subform I created a Lookup Combobox to list all of the Styles.

I updated records 1 through to 10 by inserting the related Styles.
All of the Controls highlighted in Yellow on the Main Form are NOT required once you have updated all of the records.

[unclemeat]

Hi mike
thanks for that - I had just started populating a table as instructed and it actually is the same. I was pleased I had the idea.
can I just ask a couple of things.
I have approx 50000 items in the database and some fir into 3 or 4 categories. Is this going to be a problem having say 150000 plus entries in the junction table ?
secondly, i sometimes duplicate a record because it is the same but a softcover edition say or a signed edition. Will that prove a problem in the junction table m(will it make an entry that says signed but then I change it ?
lastly, on the form where I add new records can I use a checkbox to indicate which categories the item is in ?and can I make the new entry automatically propagate the junction table or do I have to make a query or something to add new entries ?
thx for your help it’s much appreciated.
rob

Hi

If you open frmShopify this displays the Main Form based on your "Shopify" table.
I made StockID the PK

I then created the 2 tables mentioned earlier. tblMusicStyles & tblStyles
I then set Referential Integrity between these 3 tables.
Pm the Main form I created a Subform based on "tblMusicStyles" ond then in the Subform I created a Lookup Combobox to list all of the Styles.

I updated records 1 through to 10 by inserting the related Styles.
All of the Controls highlighted in Yellow on the Main Form are NOT required once you have updated all of the records.

[unclemeat]

hi Bob
sorry im not ignoring you - i was just trying to get the tables you mentined put in etc. i did read the pge you suggested as well as watched a couple of richard rost youytube vids.

the query did work, it just gives the wrong set of results - ill take a screen shot of the query for you shortly.
appreciating the help.
thx
rob

But what I asked you was:I tried the criteria expression that I offered you before I posted it. It worked for me. I would expect it to work for you. If it does not work for you, then my first suspicion would be that you have entered it incorrectly. Sometimes, we need your help to help you. Did you read any of the link that I posted for you?

If you get this criteria expression working correctly, you will need to make the change in every query that uses your original criteria expression for them to work correctly

Have you read anything on relational database design and relationships, Primary keys, Foreign Keys Normalisation?

I you restructure your database tables most of your queries will be redundant.