what is PK and FK ?
sorry if im a goose at this.
what is PK and FK ?
sorry if im a goose at this.
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.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
If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
Bob Fitzpatrick
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?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
In hospital car ark atm, so cannot look at the db.
Please use # icon on toolbar when posting code snippets.
Cross Posting: https://www.excelguru.ca/content.php?184
Debugging Access: https://www.youtube.com/results?sear...bug+access+vba
Please use # icon on toolbar when posting code snippets.
Cross Posting: https://www.excelguru.ca/content.php?184
Debugging Access: https://www.youtube.com/results?sear...bug+access+vba
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
If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
Bob Fitzpatrick
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.
You can PM me if you need further help.
Good Reading https://docs.microsoft.com/en-gb/off...on-description
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
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?Can you cut and paste here a copy of what you have in the criteria row of the query
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.
If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
Bob Fitzpatrick
Please use # icon on toolbar when posting code snippets.
Cross Posting: https://www.excelguru.ca/content.php?184
Debugging Access: https://www.youtube.com/results?sear...bug+access+vba
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.
You can PM me if you need further help.
Good Reading https://docs.microsoft.com/en-gb/off...on-description
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.
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.