?
Solved

DAO compare records.

Posted on 2008-10-31
12
Medium Priority
?
185 Views
Last Modified: 2013-11-28
I am using the DAO recordset in a Menu Ordering Access database with SQL server 2005 backend. I have 2 tables Menu Table and Order table. Since in the form I need to have shown the full daily Menu and let the customer just enter the quantity. The only way I can think of is to pass all the records in the particular day menu to the Order (FKs) table once the customer is to make the order, since almost all the items WILL be ordered anyways. This way I bring up the Order Table subform (with the Menu Table related FK items) and the customer can enter the quantity.
To do this I need to first check via RecordCount if the queried Order table has any records if not Insert them. What I want to do is if there ARE records, to compare them with the menu table.  I am trying to find out if there is a simple way to search a record via DAO reordset.
0
Comment
Question by:Papote
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 4
12 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 22860604
Papote,

First, If one Order can contain many Menu Items...
...And One Menu Item can be in many different Orders,...
... then you need a "Junction Table".

See the screenshot for a sample layout.


So, are you basically saying that you want every new order to include all the MenuItems?
If so, then here is a sample to get you started.
You will have to add your own validation for the records

JeffCoachman
100.JPG
Access-EEQ23864798MenuOrders.mdb
0
 

Author Comment

by:Papote
ID: 22861238
Well these are the tables I had:

MenuItem (Table)
      MenuItemID (PK)
      FoodID (FK from Food Table)
      MenuDayID (FK)

Order (Table)
      OrderID (PK)
      CutomerID (At the moment there are only 6 Customers, FK to Customer Table)
      DateOrdered

OrderItem (Table)
    OrderID (PK, FK to Oder)
    MenuItemID (PK, FK to MenuItem)  
    Quantity
I've been told to run a append query to use an append query to fill the OrderItem and related Order tables.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 22865945
Papote,

   "I've been told to run a append query to use an append query to fill the OrderItem and related Order tables."

That is basically what my sample does.
Did you even try it?

JeffCoachman
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

Author Comment

by:Papote
ID: 22871586
Sorry I didn't notice that there was a database attached.
0
 

Author Comment

by:Papote
ID: 22871736
Ok, I see how that works but I want to restrict it from compying the same records over and over again everytime I hit the "Load Menu Items" button.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 22873973
Papote,

Then the code gets complicated in order to account for every user action.
But I can add this one mod.

JeffCoachman
Access-EEQ23864798MenuOrders.mdb
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24340928
Papote,

My sample does as requested.

Why the delete request?

JeffCoachman
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24342348
Papote,

<I want to restrict it from compying the same records over and over again everytime I hit the "Load Menu Items" button.>
...This is what the secod sample does:
http://filedb.experts-exchange.com/incoming/2008/11_w45/74520/Access-EEQ23864798MenuOrders.mdb

Did you test it?

Jeff
0
 

Author Comment

by:Papote
ID: 24345875
I went another route and made a routine that creates all the records from the menu table to the order table and if there are records, with a 2 dimension For loop will check each record if different. If the menu was changed then it will add or delete the record from the order table. I used DAO recordsets.
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 1500 total points
ID: 24346039
Yes but this this information is only now being revealed.

I posted the sample db 5 months ago, it fulfillied all that you had asked for up until that time.
Therefore, within 21 day of that post you should have accepted it or explained why you could not.

Since that time you were still active on this site, posting over 20 questions.
As far as I know alerts are sent out on questions that are inactive for more than 21 days.

Also bear in mind that this My original sample did all that was requested in the original question.
The "second" sample was an additional request that I also fullfilled above and beyond the original question.

In other words, 21 days past the last post date, my sample did exactly what was requested.
Therefore it should be accepted.

I am glad you found a solution yourself, but this does not change what should have happened 4 moths ago.

JeffCoachman
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24349594
:-)
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question