A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

AARGGH!! How to store data - HEEELLLPP!



 
 
Thread Tools Display Modes
  #11  
Old August 26th, 2009, 05:49 PM posted to microsoft.public.access.tablesdbdesign
Phlipper via AccessMonster.com
external usenet poster
 
Posts: 9
Default AARGGH!! How to store data - HEEELLLPP!

Hi Allen,

If you could explain how to write that append query, that would be great.

Many thanks

Phil

Allen Browne wrote:
There are ways to populate the Issue table automatically when you add a new
mag. If you populate it with (say) 600 issues, that would cover the next 50
years. If you wish, I can explain how to create such an append query: let us
know if ALL your products will only ever be monthlies, of if you anticipate
the could be weeklies, quarterlies, etc.

When you are ready to send out a particular issue, you would create a query,
and type the date into the Field row, e.g.:
#1/1/2007#
Then in the Criteria row under this, enter:
Between [FirstIssue] And [LastIssue]
Specify the specific product if you wish.
The query then returns the people who should get that issue.

Thanks Allen,

[quoted text clipped - 50 lines]

Phil


--
Message posted via http://www.accessmonster.com

  #12  
Old August 26th, 2009, 06:12 PM posted to microsoft.public.access.tablesdbdesign
Fred
external usenet poster
 
Posts: 1,451
Default AARGGH!! How to store data - HEEELLLPP!

Hello Phil,

Answering your quesitons....

Not sure what you meant by "instance". If, by that you mean an instance of
one ad being in one magazine for one month, for better or for worse my
structure does not have that. And so such a record would not exist or be
created. All answer are derived from looking at the purchased "block" of
ads in a particular magazine.

The one table would hold it for all of the magazines. The "ProductID"
field would identify which magazine the OrderItem is for.

Hope that helps a little.




  #13  
Old August 27th, 2009, 03:08 AM posted to microsoft.public.access.tablesdbdesign
Allen Browne
external usenet poster
 
Posts: 11,706
Default AARGGH!! How to store data - HEEELLLPP!

1. Create a table with just one field:
CountID Number
Mark it the primary key, and save the table as (say) tblCount.
Enter 12 records -- the numbers 0 to 11.
Close.

2. Create a new query using this table.
In the first column of the Field row, enter:
ProductID: 99
In the next column, enter:
IssueDate: DateAdd("m", [CountID], #1/1/2009#)
Test: you should see 12 rows, with 2 columns like this:
99 1/1/2009
99 2/1/2009
99 3/1/2009
and so on.

3. Change the query to an Append query (Append on the query menu/ribbon.)
Access asks what table you want to append to: it's the Issue table. You now
have a new row showing in the query design grid, and it should map the
ProductID and IssueDate columns to the matching fields in the Issue table.

4. At this point, Access has build a sample query for you. To see it, switch
to SQL View (View menu.) It will look like this:
INSERT INTO ...
SELECT ...
FROM ...
You need a string like this in your VBA code. Instead of the 99, we will
concatenate the actual product number into the string. Instead of the
1/1/2009, we will concatenate the first date into the string.

5. Ultimately, your code will execute this string, like this:
strSql = "INSERT INTO ... " & Me![ProductID] & " AS ProductID, ...
With dbEngine(0)(0)
.Execute strSql, dbFailOnError
MsgBox .RecordsAffected & " issue(s) appended."
End With

More info about execute:
http://allenbrowne.com/ser-60.html

If you end up doing this often, this utility helps get the sample SQL
statement from the utility into your code:
http://allenbrowne.com/ser-71.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"Phlipper via AccessMonster.com" u53406@uwe wrote in message
news:9b2e75eb31232@uwe...
Hi Allen,

If you could explain how to write that append query, that would be great.

Many thanks

Phil

Allen Browne wrote:
There are ways to populate the Issue table automatically when you add a
new
mag. If you populate it with (say) 600 issues, that would cover the next
50
years. If you wish, I can explain how to create such an append query: let
us
know if ALL your products will only ever be monthlies, of if you
anticipate
the could be weeklies, quarterlies, etc.

When you are ready to send out a particular issue, you would create a
query,
and type the date into the Field row, e.g.:
#1/1/2007#
Then in the Criteria row under this, enter:
Between [FirstIssue] And [LastIssue]
Specify the specific product if you wish.
The query then returns the people who should get that issue.

Thanks Allen,

[quoted text clipped - 50 lines]

Phil


--
Message posted via http://www.accessmonster.com

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 10:18 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.