CJ beat me to it. When you have multiple tables, you do not help yourself.
My question is just, how do I know from which table to pick the products?
You solve that problem by not having multiple tables. You absolutely MUST read up on the concept of "normalization" before taking this too far. Many of us will tell you the same exact thing because we know WHY we think a particular idea is good. And the idea of having multiple tables that describe essentially the same thing - pet food - is one of the NOT SO GOOD ideas.
I wonder if your problem is that you have (pardon the metaphor) dived into the deep end of the pool when you should still be working the shallow end where it is harder to get in over your head. Some serious reading on normalization and some contemplation of queries that include WHERE clauses might help.
Example: If every type of pet food has the same characteristics but is just targeted to different animals, then add one field to the table for animal type. Then a QUERY from the pet food table will list all examples of CAT food with something like this:
SELECT ProdIDCode, ProdName, ProdMaker, ProdPkgSize, ProdPrice, ProdDescription, etc FROM tblPetFood WHERE ProdAnimal = "CAT" ;
Then imagine the same query for the animals "DOG" and "LLAMA" and "WOMBAT" and "CAPE EALAND BUFFALO" (or whatever else you stock.)
You can do updates that include WHERE clauses when you get that Excel spreadsheet. All you need is to import the Excel data to a working table and then write an INSERT INTO (append) query with appropriate WHERE-type selectivity.
As to your bonus table? Consider a table with these fields: < BonusCode, BonusBuy, BonusGet, BonusDiscount, BonusOneBuy >. Then (with Bonus Code being just an arbitrary number)
<1, 5, 1, 100, TRUE> would be Discount 1 = buy 5 at one time (BonusOneBuy flag true), get 1 free (100% discount).
<2, 4, 1, 50, TRUE> would be Discount 2 = buy 4 at one time, get 1 half-off (50% discount).
<3, 10, 10, 20, TRUE> would be Discount 3 = buy 10, get another 10 (in same purchase at 20% discount) - which is the same as - buy 20 and get 10% off.
<4, 9, 1, 100, FALSE> would be Discount 4 = Buy 9 across multiple purchases (BonusOneBuy flag false). We will track your purchases. When you buy the 9th one, you get the 10th one free (100% discount).
To support the latter kind of discount, you need a transaction-based table to remember that customer's previous purchases.
If you use this kind of discount table, it doesn't matter what kind of animal you are feeding. All it matters is that you have an entry in the discount table for that particular discount scheme. And it is possible using JOIN queries to tie tables together so that internally, you are working on data records that contain everything you need to manage the operation on-the-fly.
Everybody says you want to combine tables. You are trying to split tables. But here is where you will find help: Read up on how to use queries. The reason is that when you are driving things through some kind of form, you don't ever need to actually touch a table. You can drive a form through a query. Forms aren't picky. They work quite well when given data via a SELECT query. But let the QUERY touch your table(s) to pick out what you need and filter out the rest. Let the QUERY put things together for you as well as picking them apart.
Queries are your friend.