Solved

DAO compare records.

Posted on 2008-10-31
12
181 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
  • 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

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 500 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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

820 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